Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# how calculate the actual working time ?

Posted on 2014-02-01
Medium Priority
618 Views
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
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2
• 2
• +1

LVL 19

Accepted Solution

Raheman M. Abdul earned 336 total points
ID: 39826311
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

LVL 51

Assisted Solution

Gustav Brock earned 1000 total points
ID: 39826325
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

LVL 50

Assisted Solution

barry houdini earned 664 total points
ID: 39826814
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 Comment

ID: 39828199
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

LVL 51

Assisted Solution

Gustav Brock earned 1000 total points
ID: 39828294

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 Comment

ID: 39828385
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

LVL 50

Assisted Solution

barry houdini earned 664 total points
ID: 39828642
>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

LVL 51

Assisted Solution

Gustav Brock earned 1000 total points
ID: 39828865
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month11 days, 11 hours left to enroll