General discussion

Locked

DateDiff in Access Excluding weekends

By kevinsmith808 ·
Hi there, I?m using Access 2000 and want to calculate the difference between two dates in hours. I have two fields in a form [DateReferred] and [DateCompleted], both as long dates and stored in a table. I need to display a result in the same form as [Referral time] (hours). Sounds easy, but I also need to exclude Saturdays and Sundays, and ideally need to exclude hours outside of work (work being 9am to 5pm). I?m trying to do this as a public function (VBA) but having trouble even returning values to the form at present, please help.. KS

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by swleffler In reply to DateDiff in Access Exclud ...

Divide (div?) datedif by 7. Take that result and multiply by 2.

Then take the remainder (mod I believe?) of that division and execute a for loop (Note: this is not coded exactly, just pseudocode to give you an idea)


For i=0 to Remainder
if weekday(DateCompleted-i)=1 or weekday(DateCompleted-i)=7 then
counter=counter+1
end if
next i

This will check the last day and 6 or less days prior if they are a Saturday or Sunday.

Then you have
DateDiff - ((DateDiff div 7)*2) - counter

That should give you your number of weekdays. Then multiply by 8 hours.

There may be an easier way, but this should work.

Collapse -

by timfox In reply to DateDiff in Access Exclud ...

Hi KS
I haven't used the code in Access, but it looks right to me. :-)
Sorry that I couldn't figure out a neater way of doing it, but it always seems to depend on whether your start/finish days are a weekend day and which weekend day it is...
You could always return each day to a check of a weekend, counting the days in a loop (and multiplying by 8 to get your hours) - but this might only be more efficient if the date difference between dates was expected to usually be small.

To call the function - use this code statement (check for an error return value of -1):
intWorkHoursBetween([DateReferred],[DateCompleted])

This is the code for your function:

Public Function intWorkHoursBetween (strThisDate as String, strThatDate as String) As Integer

On Error GoTo intWorkHoursBetween_Err

dim intAddDays as Integer

intAddDays = 0

IF WeekDay(strThisDate) = vbSunday then intAddDays = intAddDays - 1
IF WeekDay(strThisDate) = vbSaturday then intAddDays = intAddDays - 2
IF WeekDay(strThatDate) = vbSunday then intAddDays = intAddDays - 2
IF WeekDay(strThatDate) = vbSaturday then intAddDays = intAddDays - 1

IF WeekDay(strThisDate) <> 1 AND WeekDay(strThisDate) <> 7 AND WeekDay(strThatDate) <> 1 AND WeekDay(strThatDate) <> 7 then intAddDays = intAddDays + 1

intWorkHoursBetween = ((DateDiff(strThisDate , strThatDate, "ww")*5) + intAddDays)*8
Exit

intWorkHoursBetween_Err
intWorkHoursBetween = -1
End Function

Collapse -

by p.verhagen14 In reply to DateDiff in Access Exclud ...

I created following function:
Dim day1, day2, week1, week2, weeks

Function Workdays(date1, date2)
day1 = Weekday(date1)
Select Case day1
Case 1: date1 = date1 + 1
Case 7: date1 = date1 + 2
End Select
day2 = Weekday(date2)
Select Case day2
Case 1: date2 = date2 - 2
Case 7: date2 = date2 - 1
End Select
week1 = Val(Format(date1, "ww"))
week2 = Val(Format(date2, "ww"))
weeks = week2 - week1
day1 = Weekday(date1)
day2 = Weekday(date2)
Workdays = weeks * 5 + day2 - day1 + 1
End Function
This method calculates in whole days, you only have to add functionality on the time aspect.

Peter

Back to Web Development Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums