Solved: Calculation not working

[marshybid]

Hi All,

I have a very large spreadsheet that contains many columns of dates (essentially this is a cycle time report)

I need to calculate the working time (during work hours only) that each step in the process is taking.

xld kindly provided me with a formula to help in doing this however it soes not appear to be calculating as expected.

I have attached an example sheet containing the formula.

Column C contains the date and time that a request was submitted, Column F contains the date and time that this submittal was approved.

Column G should calculate the total time taken between these dates/times (only counting working days - Mon to Fri, and only counting hours between 08:00 and 18:00, as per the formula)

As you can see in the attachment, this does not appear to be working as the correct result in column G should be 0:4:39!!

Can anyone help please.

Thanks,

Marshybid

[Bob Phillips]

Actually, it should be 4:48, there are seconds not showing that reduce it to 4:48:27.

The problem was that I didn't calculate whole days correctly, missing brackets

=IF(OR(F2="<Null>",C2="<Null>")," ",(NETWORKDAYS(C2,F2)-(WEEKDAY(C2,2)<=5)-(WEEKDAY(F2,2)<=5))*10/24
+(("18:00"-MIN("18:00",MOD(C2,1)))*(WEEKDAY(C2,2)<=5))+((MAX("8:00",MOD(F2,1))-"8:00")*(WEEKDAY(F2,2)<=5)))

[Bob Phillips]

ACtually, whilst a tad more inefficient, you might find this version more self-documenting

=IF(OR(F2="<Null>",C2="<Null>")," ",(NETWORKDAYS(C2,F2)-(WEEKDAY(C2,2)<=5)-(WEEKDAY(F2,2)<=5))*("18:00"-"8:00")
+(("18:00"-MIN("18:00",MOD(C2,1)))*(WEEKDAY(C2,2)<=5))+((MAX("8:00",MOD(F2,1))-"8:00")*(WEEKDAY(F2,2)<=5)))

[marshybid]

Thanks xld, that seems to have fixed it.

I spent a lot of time looking at the formula trying to fathom what was causing the error :dunno

Didn't take you very long to work it out though !!! :bow:

Marking as solved

Marshybid