How to calculate business hours elapsed in excel

0
votes

enter image description here

I need to calculate business hours elapsed in MS Excel. Here i have two dates, start and End date with respective timings. Some places i might not have end date and timings. Business hours are 7AM EST - 17 PM EST. how can i calculate number of business hours elapsed here ? (Excluding Weekends)

Tried "=IF(ISBLANK(P2),(NETWORKDAYS(O2,NOW())-1)*("17:00"-"7:00")+IF(NETWORKDAYS(NOW(),NOW()),MEDIAN(MOD(NOW(),1),"17:00","7:00"),"17:00")-MEDIAN(NETWORKDAYS(O2,O2)MOD(O2,1),"17:00","7:00"),(NETWORKDAYS(O2,P2)-1)("17:00"-"7:00")+IF(NETWORKDAYS(P2,P2),MEDIAN(MOD(P2,1),"17:00","7:00"),"17:00")-MEDIAN(NETWORKDAYS(O2,O2)*MOD(O2,1),"17:00","7:00"))", need to exclude holidays as well here.

2
excelexcel-formula
This is probably easier in VBA – Andreas
@Andreas Can you help me with some code/ formula to obtain this ? – chris
How do you want to handle work outside of business hours? as is the case for your examples. – Ron Rosenfeld
Business hours are 7 AM-17 PM, thus lets say Start time : 01-10-2019 7:00 AM and End time : 01-11-2019 8:00 AM (if end time is not available then it should consider current time), then total elapsed time should be 11 hours (it should also exclude weekends and holidays). Unfortunately below solutions doesn't help :( , really appreciate if you have some solution or way around to this @RonRosenfeld – chris
What if start time is 4 a.m. and end time is 3 p.m.? Should that be 8 hours or should that be 11 hours? – Ron Rosenfeld

2 Answers

1
votes

You can split this into three components (fraction of first day, full workdays, fraction of last day)

Lets start with the middle part. Here you can use NETWORKDAYS and subtract the start and end date. I am assuming a start date in A1 and end date in B1. In order to exclude holidays you need to maintain a list of holidays in your sheet. The formula assumes that this list is in range C1:C10. The results is multiplied by 10 as there are 10 hours in your workday.

=MAX((NETWORKDAYS(A1,B1,C1:C10)-NETWORKDAYS(A1,A1,C1:C10)-NETWORKDAYS(B1,B1,C1:C10))*10,0)

For the fractions you will need to determine if the day itself is a holiday, we use the NETWORKDAYS function again as a factor which will be either 0 or 1. Now we only need to determine the hours to add for the day. Depending on the granularity you want you can consider hours, minutes or even seconds. I will use hours and minutes as a fraction (minutes/60 = hours).

For the first day you get

=MAX(17-MAX(HOUR(A1)+MINUTE(A1)/60,10),0)*NETWORKDAYS(A1,A1,C1:C10)

For the last day you get

=MAX(MIN(HOUR(B1)+MINUTE(B1)/60,17)-10,0)*NETWORKDAYS(B1,B1,C1:C10)

Putting it all together leaves us with:

=MAX(17-MAX(HOUR(A1)+MINUTE(A1)/60,10),0)*NETWORKDAYS(A1,A1,C1:C10)+MAX((NETWORKDAYS(A1,B1,C1:C10)-NETWORKDAYS(A1,A1,C1:C10)-NETWORKDAYS(B1,B1,C1:C10))*10,0)+MAX(MIN(HOUR(B1)+MINUTE(B1)/60,10)-10,0)*NETWORKDAYS(B1,B1,C1:C10)
0
votes

I believe this UDF will do what you need.

It calculates the hours and returns it as a float, then you need to multiply that with 24 to get the hours.

Function workhours(startdate As Date, enddate As Date)

    Opentime = "7:00"
    Closetime = "17:00"

    Fulldays = Int(enddate - startdate) - 1

    DayOneHours = CDate(Year(startdate) & "-" & Month(startdate) & "-" & Day(startdate) & " " & Closetime) - startdate
    BeforeOpen = CDate(Year(startdate) & "-" & Month(startdate) & "-" & Day(startdate) & " " & Opentime) - startdate
    HoursDayOne = DayOneHours - BeforeOpen

    If enddate < CDate(Year(enddate) & "-" & Month(enddate) & "-" & Day(enddate) & " " & Opentime) Then
        HoursLastDay = 0
    Else
        HoursLastDay = enddate - CDate(Year(enddate) & "-" & Month(enddate) & "-" & Day(enddate) & " " & Opentime)
    End If

    workhours = Fulldays * (CDate(Closetime) - CDate(Opentime)) + HoursDayOne + HoursLastDay

End Function

Use it in Excel like:

=workhours(A1,B1)*24

enter image description here