Business Hours for more than 24 hours

[kiran5]

Hi there,

I have a 'Start date & time' in A2,A3... cell and 'End date & time' in B2,B3... and a 'holiday list' in E2:E3.
My business hours starts from 8AM to 5PM.

The below formula gives me the actual business hours spent for a request which is in C2,C3...

=(NETWORKDAYS(A2,B2,E$2:E$3)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(B2,B2,E$2:E$3),MEDIAN(MOD(B2,1),"17:00","8:00"),"17:00")-MEDIAN(NETWORKDAYS(A2,A2,E$2:E$3)*MOD(A2,1),"17:00","8:00")


This is working and showing the correct business hours spent, where as if it exceeds 24 hours, then it shows the 0:00:00 and from then it shows incorrect hours.

Can someone look onto it and give me the right formula to evaluate the business hours spent even it exceeds 24:00:00 please.?

Thanks

 

[Debraj]

Hi Kiran..

In The cell, where you have apply the formula..
Change the Cell's Number Format > Custom to

[HH]:MM:SS

 

[Hui]

Try changing the Cells format to a Custom Number format using either:

Select the cell
Ctrl 1
Number Tab
Custom
Enter either of the following in the Type: dialog
[h]:mm:ss
or
[h]:mm