Time difference excluding weekends and holidays in hours from now.

[medioman]

I would like to add time elapsed since an entry was made excluding weekends public holidays and calculating 8 hours a day (from 6am to 2 pm) since the entry was made till the generation of the report.

Any ideas how shall I approach this in terms of programming ?

Thanks alot for your time.

 

[GinaWhipp]

Well, this should get you started...

'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
On Error GoTo CalcWorkDays_Error
'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - (DateDiff("ww", dtmStart, dtmEnd, 7) + DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHolidays", "[hHolidayDate] between #" & dtmStart & "# And #" & dtmEnd & "#")
CalcWorkDays_Exit:
On Error Resume Next
Exit Function
CalcWorkDays_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit
End Function

Then multiply by 8. Oh, you're going to need a tblHolidays, here you go...
http://www.access-diva.com/downloads.html (Third one down under Access Downloads)