Quote Originally Posted by daddylonglegs View Post
How do you make that 6 days 2.5 hours, isn't it 6 days and 7.5 hours?

Assuming you have start time and date in A2 and end time and date in B2 then this formula will give the result in total hours, e.g. 55:30

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

format as [h]:mm

If you want to convert that to 6 days and 7.5 hours then, assuming that formula in C2 use this formula in D2 to convert

=INT(C2*3)&" days "&FIXED(MOD(C2*24,8),2)&" hours"
Hi,
Am now also have a similar situation and i tested the above formulas and they do calculate and give results. However,to apply these formula to my data, I seem to be curious about the followings:-

In formula
=(NETWORKDAYS(A2,B2)-1)*("17:00"-"09:00")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"17:00","09:00"),"17:00")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"17:00","09:00")
can you help to clarify what is number "1" refers to, please?

In formula
=INT(C2*3)&" days "&FIXED(MOD(C2*24,8),2)&" hours"
is number "8" refers to how many working hours per day? If it is, I shall put 9 instead because our working hours is from 8.30am-5.30pm
I still cannot figure out where has number "3" and number "2" come from.Please clarify.

Thank you in advance.