Date/Time Calculation which Excludes Weekends & Holidays

[FranD]

I need to add a control to my report which calculates the total hours required to complete projects. I have two date/time fields - one for both the date & time logged in and one for the date & time logged out. I added a calculated control which successfully calculates the difference, in hours, between those two dates - but it includes all weekend and holiday hours.

How can I exclude those hours?

SOME ONE PLEASE HELP! I am getting really frustrated and need to wrap this up quickly!!

 

[dennyryan]

Here is an example that involves setting up calculated fields in a query to arrive at the number of weekend days that are in your date range. There may be some holes in it but maybe it will give you some ideas:

Weeks: int((date1 - date2)/7)
DaysLeftover: (date1 - date2) mod 7
WeekendDays: Weeks * 2 + iif(Weekday(Date2)Between 2 and 6
and Weekday(date2) + DaysLeftOver = 7,1,
iif(Weekday(Date2)Between 2 and 6
and Weekday(date2) + DaysLeftOver > 7,2,0))


My calculation for the WeendEndDays doesn't take into account the possibility
of date2 being a weekend. That would require more iif statements. It
can be done but just makes it harder to read. I started out with the
helpful assumption that Date2 is always a weekday.

Denny

 

[WISEWARE]

Here is a much simpler piece of code which calculates working days (mon-fri) between two dates. It does have the disadvantage of looping, but this is not usually a problem unless you are comparing dates between millions of years! To exclude holidays just add another if statement along the lines of

if left(tempdate,5) = "25/1" then goto dontadd

just add a few if's for all holidays you want to exclude.

the code is.....


Private Sub Command0_Click()

Dim tempdate As Date
Dim weekdays As Integer

weekdays = 0 'initialise weekdays

tempdate = Me!date1 'me!date is the start date off a form

While tempdate <= Me!date2 'me!date2 is the end date off the form

If weekday(tempdate) = 7 Then GoTo dontadd 'if this is a saturday don't increment weekdays

If weekday(tempdate) = 1 Then GoTo dontadd 'if this is sunday don't increment weekdays

weekdays = weekdays + 1 'this is a weekday (non-fri) so increment by 1

dontadd:
tempdate = tempdate + 1 'move on to next date

Wend

msgbox "There are " & weekdays & " weekdays between the 2 dates" 'msgbox the result

End Sub

Hope you find this useful.