Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. I agreed to these rules
Join Date
31st May 2007
Posts
12

Calculate Elapsed Time Based On Business Hours

Hi all,

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?

Excel Video Tutorials / Excel Dashboards Reports

2. Re: Turn Around Time Based On Opening Hours

Hi

What you want is explained here: http://www.cpearson.com/excel/DateTimeWS.htm

Wigi
Last edited by Wigi; December 8th, 2007 at 20:51. Reason: typo

3. I agreed to these rules
Join Date
31st May 2007
Posts
12

Re: Turn Around Time Based On Opening Hours

Brilliant .

Thanks

Excel Video Tutorials / Excel Dashboards Reports

5. Re: Calculate Elapsed Time Based On Business Hours

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

=(NETWORKDAYS(A2,B2)-1)*("18:00"-"08:00")+MOD(B2,1)-MOD(A2,1)

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.

Excel Video Tutorials / Excel Dashboards Reports

6. I agreed to these rules
Join Date
31st May 2007
Posts
12

Re: Calculate Elapsed Time Based On Business Hours

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.

Excel Video Tutorials / Excel Dashboards Reports

7. Re: Calculate Elapsed Time Based On Business Hours

Originally Posted by fwiz
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.

8. Re: Calculate Elapsed Time Based On Business Hours

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

=SUM(IF(ABS(MOD(A2+(ROW(INDIRECT("1:"&ROUND((B2-A2)*1440,0)))-0.5)/1440,1)-13/24)<=LOOKUP(WEEKDAY(A2+(ROW(INDIRECT("1:"&ROUND((B2-A2)*1440,0)))-0.5)/1440,2),{1,6,7;5,4,3})/24,1))/1440

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...

=SUM(IF(MOD(A2+(ROW(INDIRECT("1:"&ROUND((B2-A2)*1440,0)))-0.5)/1440,1)>=LOOKUP(WEEKDAY(A2+(ROW(INDIRECT("1:"&ROUND((B2-A2)*1440,0)))-0.5)/1440,2),{1,6,7;8,9,10})/24,IF(MOD(A2+(ROW(INDIRECT("1:"&ROUND((B2-A2)*1440,0)))-0.5)/1440,1)<=LOOKUP(WEEKDAY(A2+(ROW(INDIRECT("1:"&ROUND((B2-A2)*1440,0)))-0.5)/1440,2),{1,6,7;18,17,16})/24,1)))/1440

This is also an array formula which must be confirmed with CTRL+SHIFT+ENTER

This part

{1,6,7;8,9,10}

gives the opening time for MF (8), SAT (9) and SUN (10) and this part

{1,6,7;18,17,16}

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.

Excel Video Tutorials / Excel Dashboards Reports

9. I agreed to these rules
Join Date
15th May 2015
Posts
1

Re: Calculate Elapsed Time Based On Business Hours

The formula posted by DaddyLongLegs worked great! However, we are using it to measure turn around time based on another company's business hours, so if the 2nd entry is before the other company's business hours begin, it adds back time. Any way to resolve that?

Excel Video Tutorials / Excel Dashboards Reports

10. Re: Calculate Elapsed Time Based On Business Hours

Hi meeshjac, welcome to OzGrid.

Regards,

S O

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno