Working Days And Hours Between Two Dates And Times

**qaiztan** · 10-16-2012, 05:47 AM

Can anyone help me with a formula that gives the working hours between two dates?

Sample is below:
Create Date Fixed Date
8/16/2012 19:29 8/23/2012 22:15

You have Holiday List
8/13/2012
8/14/2012

And working hours:
DayStart 9:00:00
DayEnd 17:00:00

Thanks a lot for your help!

**Tartarus** · 10-16-2012, 10:48 AM

121016 Time Difference.xlsxThis is what I worked out for you.

=IF(StartDate>EndDate,0,IF(DATEDIF(StartDate,EndDate,"d")=0,IF((StartDate-INT(StartDate))>DayEnd,0,MIN(DayEnd,EndDate-INT(EndDate))-(StartDate-INT(StartDate))),IF((StartDate-INT(StartDate))>DayEnd,0,DayEnd-(StartDate-INT(StartDate)))+(MIN(DayEnd,EndDate-INT(EndDate))-DayStart))+(IF(NETWORKDAYS(StartDate+1,EndDate-1,Holidays)<0,0,NETWORKDAYS(StartDate+1,EndDate-1,Holidays))*(DayEnd-DayStart)))*24

In case you wish to trouble-shoot this formula I attach a workbook containing its development notes.

**Fergastolo** · 12-15-2012, 08:19 AM

this doesn't work for me. Im trying to put the exact same code (changing the cells of course), and it doesn't work on my spreadsheet.

It works only if the date is the same for both starting and ending dates.

**daddylonglegs** · 12-16-2012, 03:57 PM

Assuming "Create" date/time in A2 and "Fixed" date/time in B2, with holiday list in H2:H10 try this formula for total working hours between the two

=(NETWORKDAYS(A2,B2,H$2:H$10)-1)*(DayEnd-DayStart)+IF(NETWORKDAYS(B2,B2,H$2:H$10),MEDIAN(MOD(B2,1),DayStart,DayEnd),DayEnd)-MEDIAN(NETWORKDAYS(A2:A2,H$2:H$10)*MOD(A2,1),DayStart,DayEnd)

format result cell as [h]:mm