Calculation of business hours in Excel

[Jayden]

Hi friends,

I have created a formula in excel where it calculates business hour and following are the code for the same
=(NETWORKDAYS(D9,E9)-1)*("17:00"-"08:00")+IF(NETWORKDAYS(D9,E9),MEDIAN(MOD(E9,1),"17:00","08:00"),"17:00")-MEDIAN(NETWORKDAYS(D9,E9)*MOD(D9,1),"17:00","08:00")

Where Start time D9 and End Time E9.

But this formula doesn't work properly when the days are same.

Can anyone fix this issue for me.

[Darsheet]

Try to use IF statement to reflect entries on the same day

=IF(NETWORKDAYS(D9,E9)<2,(E9-D9)*24,(NETWORKDAYS(D9,E9)-2)*8+(17-HOUR(D9))*(HOUR(D9)<=17)+(HOUR(E9)-9))

I am sure it will work for you.

[Glenny]

As i can see in your formula that second and third NETWORKDAYS functions are not correct, the first one should use E9 twice and the second one D9 twice. So the formula should be

=(NETWORKDAYS(D9,E9)-1)*("17:00"-"08:00")+IF(NETWORKDAYS(E9,E9),MEDIAN(MOD(E9,1),"17:00","08:00"),"17:00")-MEDIAN(NETWORKDAYS(D9,D9)*MOD(D9,1),"17:00","08:00")

[lovely-lace]

=(NETWORKDAYS(D9,E9)-1)*("17:00"-"08:00")+IF(NETWORKDAYS(E9,E9),MEDIAN(MOD(E9,1),"1 7:00","08:00"),"17:00")-MEDIAN(NETWORKDAYS(D9,D9)*MOD(D9,1),"17:00","08:00 ")


hi all, for the above formula...y the second part have to use IF but in the last part not using IF and using MEDIAN(NETWORKDAYS(D9,D9)*MOD(D9,1)?