Working out Turnaround Time based on three dates/times

[david1987]

Hi,

I want to work out the number of days we have a product in house subtracting the time on hold and excluding weekends.

I have the order start date in the following formats 03/12/12 and 03/12/12 09:45

I have the shipment date in the following formats 10/12/12 and 10/12/12 10:22

I have time on hold in the following formats 15.57 which represents 15 days 13 hours 42 minutes.

Can anyone tell me a formula to work out the shipment date - order start date while also subtracting the number of hours on hold and excluding weekends from this.

Thanks

[:) Sixthsense :)]

Please attach a sample workbook with expected output for better understanding.

[david1987]

Attached is a working example of this.

Thanks

[:) Sixthsense :)]

How you are getting 20 hours 45 minutes in F5 cell?

[Kevin UK]

Hi David

have a look at the this Link. Not quite what you are asking for, but is similar to what you require!

[daddylonglegs]

Try this formula in G2 copied down

=NETWORKDAYS(A2,C2)-1+MOD(C2,1)-MOD(A2,1)-E2

Format as number and that will give you the decimal days (excluding weekends and time on hold)

[david1987]

Thanks for that, that is giving me a decimal, which does make sense given my previous explanation.

I should have said earlier, the hours on hold is from within a framework of 10am - 10pm Monday to Friday. Is there any way of calculating the days within that framework?

For example, if we put something on hold at 9pm on Tuesday and it comes off hold at 11am on the Wednesday, it will have only been on hold for 2 hours, rather than 14 hours.

[daddylonglegs]

.....the hours on hold is from within a framework of 10am - 10pm Monday to Friday......

How do you know when it is put on hold, you only show the duration of the hold not the start/end times?

Can you indicate the required results for some rows, e.g. row 5?

[david1987]

Hi there,

Our opening hours are Mon-Friday 10am to 10pm, so a working day is 12 hours.

It is irrelevant when it goes on hold or comes off hold, because the time on hold will always be between the order start date and shipment date - the only important thing is that the hours on hold is removed from the time between the order start and shipment date.

However, the hours on hold reported in the above spreadsheet works within the opening hours of the factory, not on a 24 hour scale.

Any help is appreciated - I appreciate this is rather unique!

Thanks

David

[daddylonglegs]

OK try this formula in row 2 copied down

=(NETWORKDAYS(A2,C2)-1)*("22:00"-"10:00")+MEDIAN(MOD(C2,1),"10:00","22:00")-MEDIAN(MOD(A2,1),"10:00","22:00")-E2

format as number to get decimal days or if you want to show hours and minutes custom format as [h]:mm

Example:

For row 5 that will give you 23:22 which is 12 hours for each day, Wed 21st, Thu 22nd and Fri 23rd + 8:01 on Monday 26th = 44:01 in total minus 20:38 (0.86 days) gives you 23:22 (rounded to the nearest minute)

[david1987]

Thanks very much for that, works great, greatly appreciated!

DAvid

[david1987]

Final question on this,

I've changed the factory hours to 06:00 to 22:00 and have adjusted the formula accordingly.

However, I want to create a decimal day based on the 16 hour day that I have - rather than it changing the decimals to a 24 hour day.

For example, I have my overall time as 36 hours, I want this to be a decimal day of 2.25 rather than 1.5, suiting a 16 hour day format rather than a 24 hour format.

Help is greatly appreciated

Thanks

David