• Status: Solved
• Priority: Medium
• Security: Public
• Views: 631

# how calculate the actual working time ?

Hi,

I have a real data from a database. The difference between two dates give me the real time, wheres, I want the actual working time. for example:

Time one                                 Time two                             difference
31/1/2014 16:00:00             03/02/2014 08:00:00                       64 hours

While the actual working time is: one hour (which is from 16.00 to 17.00) and the rest of hours was the weekend.

how can I calculate the actual working time? if I have 9 hours /day in 5 days/ week

Thanks
0
• 3
• 2
• 2
• +1
6 Solutions

Senior Infrastructure Support Analyst & Systems DeveloperCommented:
Use this sheet
Replace the holiday dates in the list
Hope working times are correct

Formula used to calculate elapsed times is:

=IF(OR(A4="",B4=""),"",(NETWORKDAYS(A4,B4,\$F\$4:\$F\$12)-1)*(\$E\$5-\$E\$4)+IF(OR(ISNUMBER(MATCH(INT(B4),\$F\$4:\$F\$12,0)),WEEKDAY(B4,2)>5),\$E\$5,MEDIAN(MOD(B4,1),\$E\$5,\$E\$4))-IF(OR(ISNUMBER(MATCH(INT(A4),\$F\$4:\$F\$12,0)),WEEKDAY(A4,2)>5),\$E\$4,MEDIAN(MOD(A4,1),\$E\$5,\$E\$4)))
TimeDifferenceExclHolidays.xlsx
0

CIOCommented:
You can use this function:
``````Public Function ISO_WorkTimeDiff( _
ByVal datDateTimeFrom As Date, _
ByVal datDateTimeTo As Date, _
Optional ByVal booNoHours As Boolean) _
As Long

' Purpose: Calculate number of working minutes between date/times datDateTimeFrom and datDateTimeTo.
' Assumes: 5 or 6 working days per week. Weekend is (Saturday and) Sunday.
' Returns: "Working minutes". Divide by 60 to obtain working hours.
' Limitation: Does not count for public holidays.
'
' May be freely used and distributed.
' 2001-06-26. Gustav Brock, Cactus Data ApS, Copenhagen
'
' If booNoHours is True, time values are ignored.

' Specify begin and end time of daily working hours.
Const cdatWorkTimeStart   As Date = #8:00:00 AM#
Const cdatWorkTimeStop    As Date = #5:00:00 PM#
Const cbytWorkdaysOfWeek  As Byte = 5

Dim bytSunday             As Byte
Dim intWeekdayDateFrom    As Integer
Dim intWeekdayDateTo      As Integer
Dim datTimeFrom           As Date
Dim datTimeTo             As Date
Dim lngDays               As Long
Dim lngMinutes            As Long
Dim lngWorkMinutesDaily   As Long

' No special error handling.
On Error Resume Next

If DateDiff("n", datDateTimeFrom, datDateTimeTo) <= 0 Then
' Nothing to do. Return zero.
Else

' Calculate number of daily "working minutes".
lngWorkMinutesDaily = DateDiff("n", cdatWorkTimeStart, cdatWorkTimeStop)

' Find ISO weekday for Sunday.
bytSunday = Weekday(vbSunday, vbMonday)

' Find weekdays for the dates.
intWeekdayDateFrom = Weekday(datDateTimeFrom, vbMonday)
intWeekdayDateTo = Weekday(datDateTimeTo, vbMonday)

' Compensate weekdays' value for non-working days (weekends).
intWeekdayDateFrom = intWeekdayDateFrom + (intWeekdayDateFrom = bytSunday)
intWeekdayDateTo = intWeekdayDateTo + (intWeekdayDateTo = bytSunday)

' Calculate number of working days between the weeks of the two dates.
lngDays = (cbytWorkdaysOfWeek * DateDiff("w", datDateTimeFrom, datDateTimeTo, vbMonday, vbFirstFourDays))
' Add number of working days between the two weekdays, ignoring number of weeks.
lngDays = lngDays + intWeekdayDateTo - intWeekdayDateFrom - (cbytWorkdaysOfWeek * (intWeekdayDateTo < intWeekdayDateFrom))

If Not booNoHours = True Then
' Extract begin and stop hour (time) for the working period.
datTimeFrom = TimeSerial(Hour(datDateTimeFrom), Minute(datDateTimeFrom), Second(datDateTimeFrom))
datTimeTo = TimeSerial(Hour(datDateTimeTo), Minute(datDateTimeTo), Second(datDateTimeTo))
' Adjust times before or after daily working hours to boundaries of working hours.
If DateDiff("n", datTimeFrom, cdatWorkTimeStart) > 0 Then
datTimeFrom = cdatWorkTimeStart
ElseIf DateDiff("n", datTimeFrom, cdatWorkTimeStop) < 0 Then
datTimeFrom = cdatWorkTimeStop
End If
If DateDiff("n", datTimeTo, cdatWorkTimeStart) > 0 Then
datTimeTo = cdatWorkTimeStart
ElseIf DateDiff("n", datTimeTo, cdatWorkTimeStop) < 0 Then
datTimeTo = cdatWorkTimeStop
End If

' Calculate number of working minutes between the two days, ignoring number of days.
lngMinutes = DateDiff("n", datTimeFrom, datTimeTo)
End If

' Calculate number of working minutes between the two days using the workday count.
lngMinutes = lngMinutes + (lngDays * lngWorkMinutesDaily)

End If

ISO_WorkTimeDiff = lngMinutes

End Function
``````
The expression:

ISO_WorkTimeDiff(#1/31/2014 16:00:00#, #02/03/2014 08:00:00#) / 60

returns 1.

/gustav
0

Commented:
If you have start time/date in A2 and end time date in B2 and those times will always be within work hours then this formula will be sufficient:

=(NETWORKDAYS(A2,B2)-1)*("17:00"-"8:00")+MOD(B2,1)-MOD(A2,1)

Custom format result cell as [h]:mm to get 1:00 for your example.

If you want the answer to be a decimal number then multiply the whole formula by 24 and format result cell as number, i.e.

=24*((NETWORKDAYS(A2,B2)-1)*("17:00"-"8:00")+MOD(B2,1)-MOD(A2,1))

You can inclued a holiday range in the NETWORKDAYS function if you want to exclude holidays too

If your start and end times might be outside working hours then you can use this longer version of the second formula:

=24*((NETWORKDAYS(A2,B2)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"8:00","17:00"),"17:00")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"17:00","8:00"))

See attached examples - on the last row I have the end time outside working hours so the first two formulas (marked in red) won't show correct results - the third in green is correct

regards, barry
work-hours.xlsx
0

Author Commented:
Thanks for all.

They are useful solutions, however, the problem still happen.
these solutions avoid the calculation of weekends hours and out work hours, but if the time between two dates is 24:30 hours for example. the function return 00:30:00 which will be difficult to identify whether the difference between these dates is 30 minutes or 24.30 hours

I am working on thousands of records

Thanks
0

CIOCommented:
Your statement is very confusing.

You cannot just state a time difference, you need the start time as well, and your question was not about a given time difference but about the count of workhours betweeen two times which my function returns correctly as to your example data.

If your 24:30 falls within a weekend, count of workhours will be zero.
If starting on a workday, you will recieve some workhours, say:

ISO_WorkTimeDiff(#02/03/2014 09:00#, #02/04/2014 09:30#) / 60

it will return 9.5 hours.

/gustav
0

Author Commented:
Sorry Gustav, I couldn't understand your previous comment. I am using Excel sheet and i don't know what your comment written by !!! However, I  appreciate your time and effort with me. I need something in Excel
0

Commented:
>but if the time between two dates is 24:30 hours for example. the function return 00:30:00

This is a formatting problem - if you format as hh:mm then that format is for "clock times" and is limited to 23:59. To show "elapsed times", possibly 24 hours + then use the custom format

[h]:mm

Using the square brackets means you'll see 24:30 rather than 00:30

regards, barry
0

CIOCommented:
But you have tagged the question Access.

Still, I believe you easily can create your own function in VBA in Excel as well.

/gustav
0

## Featured Post

• 3
• 2
• 2
• +1
Tackle projects and never again get stuck behind a technical roadblock.