Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Calculate Elapsed Time Based On Business Hours

  1. #1
    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. #2
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,068

    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 21:51. Reason: typo
    Regards,

    Wigi

    Excel MVP 2011-2014



    For more Excel memes: visit http://www.wimgielis.be ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  3. #3
    Join Date
    31st May 2007
    Posts
    12

    Re: Turn Around Time Based On Opening Hours

    Brilliant .

    Thanks

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,718

    Re: Turn Around Time Based On Opening Hours

    fwiz, please share your final solution by posting it here, thanks.

  5. #5
    Join Date
    24th April 2007
    Location
    England
    Posts
    675

    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 23:18.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    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. #7
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,068

    Re: Calculate Elapsed Time Based On Business Hours

    Quote 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.
    Regards,

    Wigi

    Excel MVP 2011-2014



    For more Excel memes: visit http://www.wimgielis.be ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  8. #8
    Join Date
    24th April 2007
    Location
    England
    Posts
    675

    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 09:38.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Elapsed Hours From Date & Time
    By roce in forum EXCEL HELP
    Replies: 2
    Last Post: August 9th, 2008, 10:26
  2. Calculate Hours Worked After Specific Time
    By needhelp2008 in forum EXCEL HELP
    Replies: 11
    Last Post: February 10th, 2008, 14:49
  3. Adding business hours to a date/time
    By svrobin in forum EXCEL HELP
    Replies: 3
    Last Post: February 2nd, 2006, 22:00
  4. Calculate TIME between two hours
    By MegaThunder in forum EXCEL HELP
    Replies: 5
    Last Post: October 6th, 2005, 22:38
  5. Replies: 7
    Last Post: December 12th, 2003, 01:03

Bookmarks

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