# calculate time difference without weekends and nights

I need to calculate the time difference (in hours and minutes) without weekends and nights between two date (with day and time format).

Wich access's expression i must use?
###### Who is Participating?

Commented:
Hello fdg1966,

The UDF below returns a Date value showing total "working time" between two date/times, with you being allowed
to specify which weekdays and time ranges "count".

Use it like this, assuming Mon-Fri, 08:00-17:00...

SELECT StartAt, EndAt, WorkingHrs(StartAt, EndAt, "8:00 AM", "5:00 PM", "23456") AS TotalTime
FROM SomeTable

Regards,

Patrick
``````Function WorkingHrs(StartAt As Date, EndAt As Date, WorkStart As Date, WorkEnd As Date, Workdays As String, _
ParamArray Holidays())
' Function calculates working hours available within a specified datetime range, allowing for
' scheduled working hours, non-working days, and holidays (if desired)

' Workdays specifies days employees normally work.  For example, to use Mon - Fri, use 23456.
' To do just Tue & Thu, use 35; etc.

' assumes scheduled working hrs are the same on each working day!

Dim Counter As Long
Dim Dict As Object
Dim x As Variant
Dim y As Variant
Dim Days(1 To 7) As Boolean
Dim WorkThisDay As Boolean
Dim HolThisDay As Boolean
Dim DateToday As Date
Dim DayStart As Date
Dim DayEnd As Date

' array indicates whether that weekday is a regular workday.  Initialize to False
Days(1) = False
Days(2) = False
Days(3) = False
Days(4) = False
Days(5) = False
Days(6) = False
Days(7) = False

' populate array with results from Workdays argument
For Counter = 1 To Len(Workdays)
Days(Val(Mid(Workdays, Counter, 1))) = True
Next

On Error GoTo Cleanup

' populate holiday array
If Not IsMissing(Holidays) Then
Set Dict = CreateObject("Scripting.Dictionary")
For Each x In Holidays
For Each y In x
If Not Dict.Exists(Format(y, "m/d/yyyy")) Then Dict.Add Format(y, "m/d/yyyy"), Format(y, "m/d/yyyy")
Next
Next
End If

'loop through days in datetime range
For Counter = Int(StartAt) To Int(EndAt)
DateToday = CDate(Counter)
' determine if regular workday
WorkThisDay = Days(Weekday(DateToday, vbSunday))
' determine if holiday
If IsMissing(Holidays) Then
HolThisDay = False
Else
If Dict.Exists(Format(DateToday, "m/d/yyyy")) Then HolThisDay = True Else HolThisDay = False
End If
' if regular workday and not a holiday, figure out hrs from that day
If WorkThisDay And Not HolThisDay Then
' starts and ends on same day
If Int(StartAt) = Int(EndAt) Then
DayStart = IIf(CDate(StartAt - Int(StartAt)) > WorkStart, CDate(StartAt - Int(StartAt)), WorkStart)
DayEnd = IIf(CDate(EndAt - Int(EndAt)) < WorkEnd, CDate(EndAt - Int(EndAt)), WorkEnd)
WorkingHrs = IIf(CDbl(DayEnd - DayStart) < 0, 0, CDbl(DayEnd - DayStart))
' first day, if first day <> last day
ElseIf Counter = Int(StartAt) Then
DayStart = IIf(CDate(StartAt - Int(StartAt)) > WorkStart, CDate(StartAt - Int(StartAt)), WorkStart)
DayEnd = WorkEnd
WorkingHrs = IIf(CDbl(DayEnd - DayStart) < 0, 0, CDbl(DayEnd - DayStart))
' days in between start and end, if any
ElseIf Counter > Int(StartAt) And Counter < Int(EndAt) Then
WorkingHrs = WorkingHrs + CDbl(WorkEnd - WorkStart)
' last day, if first day <> last day
ElseIf Counter = Int(EndAt) Then
DayStart = IIf(CDate(EndAt - Int(EndAt)) > WorkStart, WorkStart, CDate(EndAt - Int(EndAt)))
DayEnd = IIf(CDate(EndAt - Int(EndAt)) < WorkEnd, CDate(EndAt - Int(EndAt)), WorkEnd)
WorkingHrs = WorkingHrs + IIf(CDbl(DayEnd - DayStart) < 0, 0, CDbl(DayEnd - DayStart))
End If
End If
Next

' convert days to hours
WorkingHrs = WorkingHrs * 24

Cleanup:
On Error GoTo 0
Set Dict = Nothing

End Function
``````
0

Commented:
the solution from cactus_data might be suitable here for you?

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23604834.html
0

Commented:
ok, what is your definition of a day as you have said not including nights
0

CIOCommented:
It will, but you may have to adjust working hours start and end:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23604834.html#a22118601

/gustav
0

Author Commented:
Just a detail: how I can add my Holidays days in your code? In the holiday array?
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.