fbpx
Search
Close this search box.

How to Calculate Working Hours Between 2 Dates [Solution]

Share

Facebook
Twitter
LinkedIn

This post builds on earlier discussion, How many hours did Johnny work? I recommend you to read that post too.

Lets say you have 2 dates (with time) in cells A1 and A2 indicating starting and ending timestamps of an activity. And you want to calculate how many workings hours the task took. Further, lets assume,

  • Start date is in A1 and End date is in A2
  • Work day starts at 9 AM and ends at 6PM
  • and weekends are holidays

Now, if you were to calculate total number of working hours between 2 given dates, the first step would be to understand the problem thru, lets say a diagram like this:

Working hours between 2 dates - how to write a formula

We would write a formula like this:

=(18/24-MOD(A1,1)+MOD(A2,1)-9/24)*24 + (NETWORKDAYS(A1,A2)-2)*9

See the above illustration to understand this formula.

Now, while this formula is not terribly long or ineffective, it does feel complicated.

May be we can solve the problem in a different way?!?

Michael left an interesting answer to my initial question, how many hours did Johnny work?

Pedro took the formula further with his comment.

The approach behind their formulas is simple and truly out of box.

Instead of calculating how many hours are worked, we try to calculate how many hours are not worked and then subtract this from the total working hours. Simple!

See this illustration:

Working hours between 2 dates - a better formula

So the formula becomes:

Total working hours between 2 dates – (hours not worked on starting day + hours not worked on ending day)

=NETWORKDAYS(A1,A2)*9 - (MOD(A1,1)-9/24 + 18/24 -MOD(A2,1))*24

After simplification, the formula becomes,

=NETWORKDAYS(A1,A2)*9 - (MOD(A1,1) -MOD(A2,1))*24 -9

=(NETWORKDAYS(A1,A2)-1)*9 +(MOD(A2,1)-MOD(A1,1))*24

Sixseven also posted an equally elegant formula that uses TIME function instead of MOD()

=(NETWORKDAYS(B3,C3)*9) - ((TIME(HOUR(B3),MINUTE(B3),SECOND(B3))-TIME(9,0,0))*24) - ((TIME(18,0,0)-TIME(HOUR(C3),MINUTE(C3),SECOND(C3)))*24)

Download the solution Workbook and play with it

Click here to download the solution workbook and use it to understand the formulas better.

Thanks to Pedro & Michael & Sixseven & All of you

If someone asks me what is the most valuable part of this site, I would proudly say, “the comments”. Every day, we get tens of insightful comments from around the world teaching us various important techniques, tricks and ideas.

Case in point: the comments by Michael, Pedro and Sixseven on the “how many hours…” post taught me how to think out of box to solve a tricky problem like this with an elegant, simple formula. Thank you very much Michael, Pedro, Sixseven and each and every one of you who comment. 🙂

Have a great weekend everyone.

PS: This weekend is my mom’s birthday, plus it is a minor festival in India. So I am going to eat sumptuously, party vigorously and relax carelessly. Next week is going to be big with launch of excel school 3.

PPS: While at it, you may want to sign up for excel school already. The free lesson offer will vanish on Wednesday.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

49 Responses to “How to Calculate Working Hours Between 2 Dates [Solution]”

  1. sixseven says:

    At the risk of sounding like a jerk, the recommended approach, and Michael's formula is basically the same as the one I proposed, with the exception that I used TIME(HOUR(),MINUTE(),SECOND()) instead of MOD.

    I certainly agree that MOD makes for a shorter, and arguably simpler formula.

  2. Chandoo says:

    @Sixseven.. You are right. I missed your equally elegant formula in the heap of responses. Sorry. I have updated the post to include your name and formula. Thanks for pointing it out. 🙂

  3. Elias says:

    Hi Chandoo,

    Maybe I’m missing something, but it looks like those formulas are not working correctly.

    When Start Date= Friday 20, Aug 5:30PM and End Date= Monday 23, Aug 5:30PM
    You get 9 hours

    However, when Start Date= Monday 23, Aug 12:00AM and End Date= Monday 23, Aug 5:30PM
    You get 17.50 hours

    Can you clarify what are the correct results for both date ranges?

    Thanks

  4. Chandoo says:

    @Elias... Sorry, but one of the assumptions of original problem was,

    Starting time is an actual working hour. In your case 12:00 AM is not the case.

    Try something like Start Date= Monday 23, Aug 10:00AM and End Date= Monday 23, Aug 5:30PM
    You get correct answer.

    Btw, the problem of fetching working hours between any given 2 dates without the condition that both start and end are not inside working hours could be even more interesting. Lets see if someone comes up with a clever answer for it.

  5. Elias says:

    Ok, I understand. Then, why the seven line of your file gets zero hours instead of four?

    Star= Sunday 22, Aug 1:00PM and End= Monday 23, Aug 1:00PM.

    Regards

  6. sixseven says:

    @Chandoo..

    Thanks. I appreciate the mention and the kind words. Also, I'm not sure if I would use the word "elegant" for my formula, but it get's the job done... 🙂 This was a good exercise for me; I haven't ever needed to use MOD before, so it was nice to see how others use it in action.

    I like the homework assignments. Please keep them coming... 🙂

  7. Daniel Ferry says:

    Chandoo:
    .
    Nice exercise.
    .
    My 2 cents would be:

    =NETWORKDAYS(B3,C3,B3)*9-(B3-C3-INT(B3)+INT(C3))*24
    .
    This eliminated the MODs and the subtraction of one day from the initial Networkdays calc. Nothing wrong with MOD but in this case it is just being used to return the decimal part of each date, which can also be done by subtracting the integer portion as shown. This may be clearer for some readers.

    The elimination of the subtraction is done by including the starting date in the list of holidays for the Networkdays function. We could also have chosen the ending date with the same result.

    My new course, the Excel Hero Academy, includes a significant portion on creative formula crafting:

    http://www.excelhero.com/blog/2010/09/excel-hero-academy---interested.html
    .
    Regards,

    Daniel Ferry
    excelhero.com

  8. Elias says:

    The original idea comes from http://www.cpearson.com/excel/DateTimeWS.htm. I just adapt it to this post.

    A1=Start regular working hour
    B1=End regular working hour

    A3=Project Start Date & Hour
    B3=Project End Date & Hour

    =ABS(IF(INT(A3)=INT(B3),ROUND(24*(B3-A3),2),24*($B$1-$A$1)*(NETWORKDAYS(A3+1,B3-1)+INT(24*(MOD(B3,1)-MOD(A3,1)+($B$1-$A$1))/(24*($B$1-$A$1))))+MOD(ROUND(24*MOD(B3,1)-24*$A$1+24*$B$1-24*MOD(A3,1),2),ROUND(24*($B$1-$A$1),2))))

    Regards

  9. Elias says:

    You can reduce the above formula using some auxiliary cells.

    A1=Start regular working hour
    B1=End regular working hour
    C1=Total day working hours

    A3=Project Start Date & Hour
    B3=Project End Date & Hour
    D3=Project Start Hour
    E3=Project End Hour

    =ABS(IF(INT(A3)=INT(B3),ROUND(24*(B3-A3),2),24*$C$1*(NETWORKDAYS(A3+1,B3-1)+INT(24*(E3-D3+$C$1)/(24*$C$1)))+MOD(ROUND(24*E3-24*$A$1+24*$B$1-24*D3,2),ROUND(24*$C$1,2))))

    Regards

  10. Pedro Wave says:

    @Elias, could you check the original "biggest" Pearson formula?
    You forgot this important MAX function to work correctly:
    MAX(NETWORKDAYS(StartDT+1,EndDt-1,HolidayList),0)

    @Daniel, with your last formula, it's necessary to put start or end date into the HolidayList

    @Chandoo, I agree with you when you says that the most valuable part of this site are comments and I would like that you write some comment on my blog.
    The
    =(NETWORKDAYS(A1,A2,Holidays)-1)*Hours +(MOD(A2,1)-MOD(A1,1))*24
    Where:
    - Holidays is an optional range of one or more dates to exclude from the working calendar.
    - Hours are maximum working hours in a day.
    Lets say Johnny doesn't take any lunch breaks (he has developed a taste for those higgs bosons sandwich with positron milk shake).

  11. Daniel Ferry says:

    @Pedro Wave -
    .
    That was the point.

  12. Elias says:

    @ Pedro-
    .
    Thanks for point that out, you are right we should include error control in all the proposed formulas.
    .
    I know that Person’s formula is huge, but I think we need something to control Start and End day working hours, not just total allowed work hours per day.
    .
    Also, it looks like all the formulas need to be fixed to return the correct result when the Start Date and End Date are on the same day but they are more hours than are allowed.
    .
    ie. Start Date = Thursday 19, Aug 9:00 AM and End Date = Thursday 19, Aug 9:00 PM.
    .
    .
    Regards

  13. Pedro Wave says:

    @Daniel making your formula more general if you will permit me:
    =NETWORKDAYS(B3,C3,Holidays)*Hours-(B3-C3-INT(B3)+INT(C3))*24
    Where:
    - Holidays is an optional range of one or more dates (including B3 or C3, ONLY ONE!) to exclude from the working calendar.
    - Hours are maximum working hours without breaks in a day.
    .
    @Elias, Chandoo's homework assumes a range of working hours without breaks.
    .
    If you want to expand the hours worked, you must indicate in the formula, by example: from 9:00 AM (min. start time) to 9:00 PM (max. stop time) are 12 hours and this formula works:
    =(NETWORKDAYS(A1,A2,Holidays)-1)*12 +(MOD(A2,1)-MOD(A1,1))*24
    .
    If Johnny is working at midnight, should be an end date at 12:00 AM and a start date in the next line at same hour of next day.
    .
    For working breaks in the same or between two days, forces to think in another formula ways.

  14. Elias says:

    [Quote] Pedro.
    “@Elias, Chandoo’s homework assumes a range of working hours without breaks”
    [End Quote]
    .
    Pedro did you read Chandoo’s last paragraph on the fourth comment?
    .
    [Quote] Chandoo.
    “Btw, the problem of fetching working hours between any given 2 dates without the condition that both start and end are not inside working hours could be even more interesting. Let’s see if someone comes up with a clever answer for it.”
    [End Quote]
    .
    That’s why I came with Person’s formula.
    .
    Regards

  15. Pedro Wave says:

    @Chandoo, here it is my solution to the problem of fetching working hours between any given 2 dates without the condition that both start and end are not inside working hours:
    =(DecimalHours*(NETWORKDAYS(B3,C3,HolidayList)-1)
    +MAX(IF(NETWORKDAYS(C3,C3)0,MIN(MOD(C3,1),$D$26),$D$26),$D$25)
    -MIN(IF(NETWORKDAYS(B3,B3)0,MAX(MOD(B3,1),$D$25),$D$25),$D$26))*24
    .
    It is included as Pedro Wave V.2 (Column K) in this file with other proposals until now:
    http://cid-6b219f16da7128e3.office.live.com/view.aspx/.Public/working-hours-between-2-dates-data-solution2.xls
    .
    I also added in column L, worked hours by my civil servants or Spanish government employees.

  16. Elias says:

    Ok, here is one more huge option.
    A1=DayStart time, B1=DayEnd Time
    A3=SartDate, B3=EndDate

    =IF(INT(A3)=INT(B3),MIN(B3-A3,$B$1-$A$1)*NETWORKDAYS(A3,A3,HolidayList),($B$1-MIN(MAX(MOD(A3,1),$A$1),$B$1))*NETWORKDAYS(A3,A3,HolidayList)+(MAX(MIN(MOD(B3,1),$B$1),$A$1)-$A$1)*NETWORKDAYS(B3,B3,HolidayList)+MAX(NETWORKDAYS(A3+1,B3-1,HolidayList),0)*($B$1-$A$1))*24

    Regards

  17. Pedro Wave says:

    @Chandoo, my last formula is incomplete due to lack of signs minor and major in my last comment, so it is better seen at the K column of my last file. Only worked hours are added between regular start and end working hours, excluding hours into holiday dates.

    @Elias, I modified your formula in my last file including references to a new HolidayList sheet and to DecimalHours as:
    = $D$26 - $D$25
    where:
    $D$25 = Start regular working hour
    $D$26 = End regular working hour

    @Daniel, in the G (Start Date) and H (End Date) columns are your formulas without including HolidayList. Could you give us a solution for the argument to exclude other Holidays dates?

  18. Elias says:

    @ Pedro. I like your last formula, I think it would be better if it includes the HolidayList in all NETWORKDAYS function. Just in case the StartDate or EndDate are holidays.

    @ Chandoo. My earliest post (around 9:00AM CT) didn't get post, any idea why this might happen?

    Regards

  19. Pedro Wave says:

    @Elias, thanks for reminding me. I just added the reference to HolidayList in all formulas, except into Daniel Ferry formulas, in this new file:
    http://cid-6b219f16da7128e3.office.live.com/view.aspx/.Public/working-hours-between-2-dates-data-solution3.xls
    I've included your last formula in column M. I have marked in orange half-hour discrepancy in row 19. You can keep an eye on it?
    You will agree with me that the Chip Pearson formula adds nothing to our latest proposals for resolving the problem posted by Chandoo.
    .
    @Chandoo, when a work day starts at 9 AM and ends at 6PM there are only 9 worked hours and you represent 10 hours in your images, which I corrected in my uploaded files.

  20. Elias says:

    I think this one works better than my previous one.
    .
    A1=DayStart time, B1=DayEnd time and C1=B1–A1
    A3=SartDate and B3=EndDate
    .
    =(
    ($A$1-MIN(MAX(MOD(A3,1),$A$1),$B$1))*
    NETWORKDAYS(A3,A3,HolidayList)+
    (MAX(MIN(MOD(B3,1),$B$1),$A$1)-$B$1)*
    NETWORKDAYS(B3,B3,HolidayList)+
    MAX(NETWORKDAYS(A3,B3,HolidayList),0)*$C$1
    )*24
    .
    Regards

  21. Pedro Wave says:

    @Elias, your formula give me for:
    Monday 16, Aug 10:00 AM to Tuesday 17, Aug 1:00 PM = 11 h (before 12 h)
    Sunday 22, Aug 1:00 PM to Monday 23, Aug 1:00 PM = 0 h (before 4 h), etc...
    Your above formula was better! Only one different date, now 7...
    Could you try again?

  22. Elias says:

    @Pedro. I get the correct results, maybe is the way I pasted the formula. Let’s try this one that uses the same cells you are using in your file.

    =(($D$28-MIN(MAX(MOD(B3,1),$D$28),$D$29))*NETWORKDAYS(B3,B3,HolidayList)+(MAX(MIN(MOD(C3,1),$D$29),$D$28)-$D$29)*NETWORKDAYS(C3,C3,HolidayList)+MAX(NETWORKDAYS(B3,C3,HolidayList),0)*DecimalHours)*24

    Regards

  23. Pedro Wave says:

    @Elias, error was not yours, but mine since I transformed it wrong to try.
    I entered your formula as Elias V.3 in column N in the next file:
    http://cid-6b219f16da7128e3.office.live.com/view.aspx/.Public/working-hours-between-2-dates-data-solution4.xls
    .
    @ Chandoo, my previous formula expressed in two more ways:
    =(DecimalHours*(NETWORKDAYS(B3,C3,HolidayList)-1)+ MAX(IF(NETWORKDAYS(C3,C3,HolidayList)=0,$D$29,MIN(MOD(C3,1),$D$29)),$D$28)-MIN(IF(NETWORKDAYS(B3,B3,HolidayList)=0,$D$28,MAX(MOD(B3,1),$D$28)),$D$29))*24
    .
    =(DecimalHours*(NETWORKDAYS(B3,C3,HolidayList)-1)+ IF(NETWORKDAYS(C3,C3,HolidayList)=0,MAX($D$28,$D$29),MAX($D$28,MIN(MOD(C3,1),$D$29)))-IF(NETWORKDAYS(B3,B3,HolidayList)=0,MIN($D$28,$D$29),MIN($D$29,MAX(MOD(B3,1),$D$28))))*24
    I entered this last formula as Peter V.3 in column O on the attached file.

  24. Pedro Wave says:

    I just saw that my last formula can be simplified since start is less than end of regular working hours:
    =(DecimalHours*(NETWORKDAYS(B3,C3,HolidayList)-1)+ IF(NETWORKDAYS(C3,C3,HolidayList)=0,$D$29,MAX($D$28,MIN(MOD(C3,1),$D$29)))- IF(NETWORKDAYS(B3,B3,HolidayList)=0,$D$28,MIN($D$29,MAX(MOD(B3,1),$D$28))))*24
    Or are exactly equal for some workers!

  25. seraj says:

    hi chandoo,

    I am not able to understand why 9 is used in that formula...! can u please explain...

  26. Donna says:

    can this formula be adapted to account for the working hours chaging based onthe day of the week.

    My problem is that my working hours are
    Monday to Friday 08:00:00 to 17:30:00
    Saturday 09:00:00 to 16:00:00
    Sunday 09:00:00 to 14:00:00

    i am really struggling to find an answer for this??

    Cheers

    Donna

  27. Pedro Wave says:

    @Donna, It may be a little late but here's my solution to your problem when working hours are changing based on the day of the week.
    In Excel 2010, sum this formula 3 times, once for each range:
     
    =IF(A10>B10,0,+$D$2*(NETWORKDAYS.INTL(A10,B10,Range1,Holidays)-1)
    +IF(NETWORKDAYS.INTL(B10,B10,Range1,Holidays)=0,MAX($B$2,$C$2),MAX($B$2,MIN(MOD(B10,1),$C$2)))
    -IF(NETWORKDAYS.INTL(A10,A10,Range1,Holidays)=0,MIN($B$2,$C$2),MIN($C$2,MAX(MOD(A10,1),$B$2))))
     
     
    Where:
    Range1 = "0000011" (Monday to Friday)
    Range2 = "1111101" (Saturday)
    Range3 = "1111110" (Sunday)
    Better download an Excel template from my blog:
    http://pedrowave.blogspot.com.es/2013/03/hours-between-dates.html
     
    Excel 2003 and 2007: Column D, using the auxiliary columns E:K
    Excel 2010 and later: Column C, using the new WORKDAY.INTL function
     

  28. Prashant says:

    I am calculating difference of hours betweens two days excluding weekends but i am getting 9hr less in my result. In my project they are calculating on severity basis so i include IF command in the below formula.

    I am using the below formula:

    =IF(B30<3,E30,((NETWORKDAYS(C30,D30)-1-MOD(C30,1)+MOD(D30,1))*24))

    I am using the above formula for below example but i am not getting the rigt answer.

    B30 (Severity =3 ) C30 (Opentime = 4/10/2013 3:04) D30 (Closed time = 4/14/2013 15:41) E30 (Total time including weekends = 108.6141)

    correct ans 68.96 hrs

    Here C30 is start time and D30 is closed time

    I am getting 60.61 hrs but the correct answer is 68.96 hrs.

    Ticket is opened on 10th April at 3:04am and closed on 14th April at 15:41. Here Business days are 10,11 and 12 April and if we calcluate it manually then its comes as below:

    (As open time is 3:04 AM so we have 20:96 hrs in hand from 24 hrs)

    20:96 hrs for 10th Apr + 24 hrs for 11th Apr + 24 hrs for 12th Apr = 68.96 hrs but i am getting only 60.61 hrs by the above formula.

    Please help me in this.

    • Hui... says:

      @Prashant
      E30 should be:
      =(D30-C30-((INT(D30)-INT(C30))-NETWORKDAYS.INTL(C30,D30-1,17)))*24
      = 84.6166667

      Made up of:
      Date Hours
      10 0.87
      11 1.00
      12 1.00
      13 -
      14 0.65
      Days 3.53
      Hrs 84.61666667

      • Prashant says:

        Thanks for the quick response.

        But we are not considering weekends i.e. Saturday and Sunday.

        Can you please help. Thanks in advance.

      • Prashant says:

        Here 13 and 14 April is Weekends can you please exclude them from this formula.

        Thanks..

  29. Paul K Stanley says:

    Could I throw this one open, as it is relevant in the project I'm currently undertaking.

    What if the End Date is before the Start Date?

    Based on a normal working day m-f (08:00-17:00) and using the dates and times as:
    Start: Wed 14/8/13 8am
    End: Tues 13/8/13 4pm

    My reason for this. If something arrives in the office (say telephone callback) at 4pm on Tues, we have a maximum 3-hour turnround to action it, so the action date would be the following day. The answer I'm looking to achieve for the above example is -1.00 hrs, but all I get is -19.00 hrs: 4-5pm = 1 hour and then start of next day would be 8am. The answer I'm getting is including out-of-office hours which is not what I'm after.

    Sounds strange, but hope I've explained it enough. I still wish to be able to have a start date earlier than the end date.

    Regards

    Paul S

  30. Narayanswamy says:

    Request Created Time Request Closed Time ? No of hours
    13.09.2013 07:12 PM 18.09.2013 11:11 AM need a formula to calculate no of hours considering 16 hrs of working time

  31. ramnath says:

    Hi Chandoo

    Thanks for the blog which is great help for my project.

    I have managed to custom the formula and it works fine but it throws error for below start and end date (all GMT).

    Start: 21/06/2015 17:49
    End: 22/06/2015 06:55

    =TEXT((24*(NETWORKDAYS(A4,B4,RDATA!$AC$2:$AC$71)-1)-24*((MAX(MOD(A4,1),MOD(B4,1))-MIN(MOD(A4,1),MOD(B4,1)))))/24,"dd/hh/mm")

    Not sure what the error is but will be helpful if you can point out anything in the formula.

    Best
    Ram

  32. ramnath says:

    Not sure this may help other but managed to get the desired result with this..

    =IF(AND(DAY(A2)DAY(B2),MONTH(A2)MONTH(B2),OR(MOD(A2,1)>MOD(B2,1),MOD(A2,1)<MOD(B2,1))),TEXT((24*(NETWORKDAYS(A2,B2,RDATA!$AC$2:$AC$71))-24*((MOD(A2,1)-MOD(B2,1))))/24-1,"d/hh/mm"),IF(AND(DAY(A2)DAY(B2),MONTH(A2)=MONTH(B2),MOD(A2,1)<MOD(B2,1)),TEXT((24*(NETWORKDAYS(A2,B2,RDATA!$AC$2:$AC$71))-24*((MOD(A2,1)-MOD(B2,1))))/24-1,"d/hh/mm"),IF(AND(DAY(A2)DAY(B2),MONTH(A2)=MONTH(B2),MOD(A2,1)>=MOD(B2,1),MAX(A2:B2)-MIN(A2:B2)>=6),NETWORKDAYS(A2,B2,RDATA!$AC$2:$AC$71)-IF(MOD(A2,1)<=MOD(B2,1),0,1)-1&"/"&TEXT(B2-A2,"hh")&"/"&RIGHT(TEXT(B2-A2,"hh:mm"),2),IF(AND(DAY(A2)DAY(B2),MONTH(A2)=MONTH(B2),MOD(A2,1)>MOD(B2,1),MAX(A2:B2)-MIN(A2:B2)<6),NETWORKDAYS(A2,B2,RDATA!$AC$2:$AC$71)-IF(MOD(A2,1)<=MOD(B2,1),0,1)&"/"&TEXT(B2-A2,"hh")&"/"&RIGHT(TEXT(B2-A2,"hh:mm"),2),IF(AND(DAY(A2)=DAY(B2),MONTH(A2)=MONTH(B2),MOD(A2,1)<MOD(B2,1)),NETWORKDAYS(A2,B2,RDATA!$AC$2:$AC$71)-IF(MOD(A2,1)<=MOD(B2,1),0,1)-1&"/"&TEXT(B2-A2,"hh")&"/"&RIGHT(TEXT(B2-A2,"hh:mm"),2),"")))))

  33. Talana says:

    Can someone please assist with the following ...

    Workday starts at 08h00
    Lunch 12h00 to 13h00
    Workday ends at 17h00

    I need to calculate the # of hrs worked ... excluding lunch, excluding weekends and holidays.

    A1=workdayStart
    B1=lunchStart
    C1=lunchEnd
    D1=workdayEnd

    A4=FromDateAndTime
    B4=EndDateAndTime
    C4=Total # of hrs worked ... excluding lunch, excluding weekends and holidays

    I have tried various options, but seems to be missing something here.

    thanks
    talana

    • anton says:

      hi Talana,
      wondering if you already have the answer yet, could you share with me? I got the same problem also need the formula working in excel 2007..

      thx

      • Hien says:

        Hi Talana and Anton,
        Regarding the request includes break time, I have just considered that within a day, there are two periods with different start time and end time:
        A1=workdayStart
        B1=lunchStart
        C1=lunchEnd
        D1=workdayEnd

        A4=FromDateAndTime
        B4=EndDateAndTime
        1st period:
        A1=workdayStart
        B1=lunchStart
        2nd period:
        C1=lunchEnd
        D1=workdayEnd
        Holidays can be taken into account, ex.$E$2:$E$5
        Only the time within these two periods considered working time.
        I have found this formula works well for me:
        (NETWORKDAYS(A4,B4,$E$2:$E$5)-1)*(($D$1-$A$1)-($C$1-$B$1)+IF(NETWORKDAYS(B4,B4),MEDIAN(MOD(B4,1),$B$1,$A$1)-MEDIAN(NETWORKDAYS(A4,A4,$E$2:$E$5)*MOD(A4,1),$B$1,$A$1))+IF(NETWORKDAYS(B4,B4),MEDIAN(MOD(B4,1),$D$1,$C$1)-MEDIAN(NETWORKDAYS(A4,A4,$E$2:$E$5)*MOD(A4,1),$D$1,$C$1))

        • Hien says:

          (NETWORKDAYS(A4,B4,$E$2:$E$5)-1)*(($D$1-$A$1)-($C$1-$B$1)+IF(NETWORKDAYS(B4,B4),MEDIAN(MOD(B4,1),$B$1,$A$1)-MEDIAN(NETWORKDAYS(A4,A4)*MOD(A4,1),$B$1,$A$1))+IF(NETWORKDAYS(B4,B4),MEDIAN(MOD(B4,1),$D$1,$C$1)-MEDIAN(NETWORKDAYS(A4,A4)*MOD(A4,1),$D$1,$C$1))

          => i forgot to erase holiday in some part of old formula, holiday should appear once only

  34. Sachin says:

    Hi,

    I want to calculate the hours between two dates which exclude hours friday 6:00 P.M to Sunday 6:00 P.M

  35. manish agarwal says:

    Hello,

    I am not an expert in excel formulas, I have spent almost 3-4 hrs to understand this formula =(NETWORKDAYS(A1,A2)-1)*9 +(MOD(A2,1)-MOD(A1,1))*24

    Dates are:
    Monday 16, Aug 10:00 AM (B3)
    Tuesday 17, Aug 1:00 PM (C3)

    Things that I don't understand:

    =(NETWORKDAYS(A1,A2)-1)*9

    why we subs 1, I thought if we dont subs then it will give 18 which seems logical i;e total working hours for two days

    +(MOD(A2,1)-MOD(A1,1))*24

    by multiplying 24, means we want to convert it to hours term, which gives us 3 (10 AM to 1 PM)

    But then 18-3 gives us 15 which is incorrect.

    I am lost. Please explain the formula if anyone can.

  36. manish agarwal says:

    Hello,

    Can someone explain the simplification process of the formula:

    FROM
    =NETWORKDAYS(A1,A2)*9 - (MOD(A1,1)-9/24 + 18/24 -MOD(A2,1))*24

    TO

    =NETWORKDAYS(A1,A2)*9 - (MOD(A1,1) -MOD(A2,1))*24 -9

    =(NETWORKDAYS(A1,A2)-1)*9 +(MOD(A2,1)-MOD(A1,1))*24

  37. Walter says:

    Hi,

    Just wondering if anyone has been able to modify the formula to deduction out time taken for lunch? Using this to determine time off taken so could have a start of 04/04/2017 11:00 AM and an end of 04/04/2017 4:30 PM. As lunch time is not included in the time off taken, in this example the total time off would be 5 hours (less 0.5 hrs for lunch).

  38. SEBASTIAN says:

    is it possible to do this in DAX?

  39. byundt says:

    The attached discussion is my attempt to improve Chip Pearson’s formula. The suggested formulas don't require that the beginning and ending times bracket the work shift.

    The formulas use four named ranges:
    beginning and ending (of the period being measured), which include both date and time elements
    ShiftBegin and ShiftEnd which include only the times for the start and end of the work shift

    The formulas are only valid when starting and ending dates are different!

    By way of explanation:
    1. Time consumed on the first and last days of the interval is figured using the MOD expressions.
    2. Time consumed in the interior of the interval is figured using (NETWORKDAYS -2)*Shift length. Since NETWORKDAYS excludes weekends, you may include those weekend days by using (INT(ending) – INT(beginning) -1) instead of (NETWORKDAYS-2).
    3. If the work shift spans midnight, its duration may be calculated directly using MOD. If not, the overall elapsed time (excluding intervening days) less MOD(rest end – rest start,1) is used instead.

    MOD(ending,1) returns just the time portion of the date/time serial number
    MOD(ShiftEnd – ShiftBegin,1) returns the number of hours in the shift, regardless whether that shift spans midnight or not. This expression assumes the number of hours in the interval is less than 24.

    Work time
    =IF(ShiftEnd>ShiftBegin,
    (MOD(ending,1)+1-MOD(beginning,1)) -
    MOD(MIN(MOD(ending,1),ShiftBegin)-MAX(MOD(beginning,1),ShiftEnd),1),
    MOD(MIN(MOD(ending,1),ShiftEnd)-MAX(MOD(beginning,1),ShiftBegin),1))
    + MAX(0,NETWORKDAYS(beginning,ending)-2)*MOD(ShiftEnd-ShiftBegin,1)

    Rest time
    =IF(ShiftEnd>ShiftBegin,
    MOD(MIN(MOD(ending,1),ShiftBegin)-MAX(MOD(beginning,1),ShiftEnd),1),
    (MOD(ending,1)+1-MOD(beginning,1)) -
    MOD(MIN(MOD(ending,1),ShiftEnd)-MAX(MOD(beginning,1),ShiftBegin),1))
    + MAX(0,NETWORKDAYS(beginning,ending)-2)*MOD(ShiftBegin-ShiftEnd,1)

  40. streaming vf says:

    Ahaa, C'est un dialogue délicat sur cette paragraphe dans
    cette place sur cette webpage, j'ai lu tout ça, alors maintenant je commente aussi ici.

Leave a Reply