What you want is explained here: http://www.cpearson.com/excel/DateTimeWS.htm
I've being trying t calculate an excel formula to work out a order turnaround time based on opening hours.
Example: The store is open from 8 am to 6pm monday to friday, I need to get a formula to work out how long it took from the time the order was logged until it was completed, during the course of the working day - this works fine but if the order was received on 06/12/07 at 5:20 pm then was finally completed by 7/12/07 at 11:55 the next day I need to figure out how to take into consideration of non working hours during that time. (time from 6pm to 8 am next day
example 2 - if order was sent on the 06/12/07 at 01:30pm and completed at 06/12/07 4:30 then I know its taken 3 hours to complete
example 3 - if the order was sent on the 06/12/07 at 5pm and completed next day at 07/12/07 at 10am - then I know it took 3 hours to complete
does this make sense?
Note: Chip Pearson's suggested formulas only cater for situations where both start and end time/dates are within business hours. If that's the case then you can use a simplified formula.
With order logged time/date in A2 and order completed time/date in B2 try
format result cell as [h]:mm
Note: You can also exclude holidays by using a holiday range as a 3rd argument within the NETWORKDAYS function
Last edited by daddylonglegs; December 9th, 2007 at 22:18.
I've been away for a few days and just looked at the answers in detail, they work but it will only work for my standard hours - meaning that if we are open from monday to friday from 8am to 6pm I can get a turnaround time - spot on, but although (one thing I didn't think of before) we do open weekends also - Saturdays from 9am til 5 pm and sunday from 10am til 4pm, how would I include these times in the above formula .....?
I need to build these in - but haven't got a clue since the formulas are pretty complex enough already.
Indeed, you better break down the formula into parts. Use helper columns that you can hide if you want to.Originally Posted by fwiz
Excel MVP 2011, 2012, 2013
-- Please use a meaningful title for your topic --
With start time/date in A2 and end time date in B2 this formula will give you business hours between the two, given the hours you defined above, i.e. 08:00 to 18:00 Monday to Friday, 09:00 to 17:00 Saturdays and 10:00 to 16:00 on Sundays
This is an array formula which must be confirmed with CTRL+SHIFT+ENTER
format result cell as [h]:mm
Note: assumption is that start and end times will be in hours and minutes only, no seconds
It may not be immediately obvious how to alter the above if you want to alter the opening hours so here's a (longer) more generic formula which does the same...
This is also an array formula which must be confirmed with CTRL+SHIFT+ENTER
gives the opening time for MF (8), SAT (9) and SUN (10) and this part
gives the respective closing times
You could replace these "array constants" with a table on the worksheet containing the opening and closing times for each day of the week....
Note: should also say that the above only works for time spans (between A2 and B2) of approx 45 days, you could get longer if you assume all times are shown to 5 minutes rather than 1....
Last edited by daddylonglegs; December 12th, 2007 at 08:38.
There are currently 1 users browsing this thread. (0 members and 1 guests)