How many hours did Johnny work? [Excel Homework]

Posted on August 27th, 2010 in Excel Howtos , Learn Excel - 40 comments

Story of Johnny, Hard working atom splitter:

Meet Johnny, who lives by the intersection of lane 2345, and BAG street. Johnny (or little Jon as friends call him) is a hard working family guy. He works at local nuclear power plant in a neighborhood named Sheet3. Due to a recent re-organization at work place, Jon found himself reporting to an almost crazy boss named “Bill Lumbergh”.

Lumbergh asking Johnny to split atoms manully before its too late.. or else...,Now, Lumbergh is not your everyday crazy boss, he is so much more than that. So, one day Lumbergh walks over to Jon’s desk and tells him, “Look Jon, we are having issues in the particle accelerator in the basement. It doesn’t seem to split atoms alright. So I need you to go there and split atoms manually. We got 12,789,000 atoms to be split before next weekend. So I am gonna go ahead and ask you to work on it. Ummkay?

Just when Johnny thought of uttering a curse, Lumbergh came back and reminded, “Oh Johnny, remember to log how much time you are spending splitting atoms. I need you to tell me how many hours you worked for every million atoms. Make sure you follow the latest Timesheet report formats, or else…,

Johnny muttered a couple of real ugly cuss words that are not blog worthy and went about his business of splitting atoms. He managed split only 11 million of them before the deadline given by Lumbergh. Those darned atoms!

Also, Johnny logged the time he spent for every million atoms using, well, excel. Like this:

Data - Start and End times for each task - Calculating working hours between 2 given dates

(Download the data)

Your homework:

Johnny needs your help to figure out how many hours he worked in total (as well as for each million). He is already tired hunting a missing electron in the basement alleys. So don’t tell him to count manually. He wants to have an excel formula that tells him how many hours he worked given a start and end date in cells A1 and A2 respectively. Remember,

  • Johnny never works after 6pm or before 9am
  • Johnny never works on weekends.
  • Lets say Johnny doesnt take any lunch breaks (he has developed a taste for those higgs bosons sandwich with positron milk shake).

So I am gonna go ahead and ask you to complete this homework before the weekend. When you got the correct formula, come back here and post it in comments. Ummkay?

More excel homework:

PS: If you feel lost, that is because you have not seen office space. Go watch it.
PPS: if you still feel lost, that is because you do not know NETWORKDAYS. Go learn it.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

40 Responses to “How many hours did Johnny work? [Excel Homework]”

  1. TessaES says:

    Monday 23th 5 PM till Monday 23th 4 PM ? That is -1 hour for 1.000.000 atoms! Even better: Tuesday 17th 4 PM till 2 PM, -2 hours! I wish I could work like that :)

  2. Chris Byham says:

    First, find out difference in Start / End Date, using Networkdays. So, in C2, type ‘=NETWORKDAYS(A2,B2)-1.
    In D2, Find Start Time only, using ‘=TIME(HOUR(A2),MINUTE(A2),SECOND(A2))’
    Repeat in E2 to Find End Time, ‘=TIME(HOUR(B2),MINUTE(B2),SECOND(B2))’
    In F2, Calculate diff in Start / End Time ‘=E2-D2 ‘
    Next, in G2, add Days from D3 to Time in G3 ‘=C2+F2
    Finally, Express in terms of hours, and take off time between 6pm and 9am ‘=(G2*24)-(C2*15)’

    I’ve changed a couple of the times given – cell A4 changed to 12pm, A6 to 6pm (doesn’t work at 7pm) and B10 changed to 6pm.

    Total hours worked 86.5, average 7.86 hours per million.

  3. Chris Byham says:

    Combining into one formula, looks a little like this:

    =((NETWORKDAYS(A2,B2)-1)+(TIME(HOUR(B2),MINUTE(B2),SECOND(B2))-TIME(HOUR(A2),MINUTE(A2),SECOND(A2))))*24-(NETWORKDAYS(A2,B2)-1)*15

  4. Chandoo says:

    @Tessaes… sorry.. my bad. I guess I was too busy sympathizing with Johnny. I correct the image and data file. Try now :)

  5. Luke Moraga says:

    =SUM((NETWORKDAYS(A2,B2)-2)*TIME(9,0,0),TIME(18,0,0)-MOD(A2,1),MOD(B2,1)-TIME(9,0,0))*24

    Whole days worked * hours in a working day + max possible hours to work from start to end of day + max possible hours to work from start of day to finish. Multiply by 24 to convert to hours.

    Total time worked: 100 hours and 44 minutes
    Hours per million (divide by 11): 9 hrs per million

  6. Chris says:

    D1 being 6pm
    E1 being 9am

    =(NETWORKDAYS(B3,C3)*($D$1-$E$1))-(TIME(HOUR(B3),MINUTE(B3),SECOND(B3))-TIME(HOUR($E$1),MINUTE($E$1),SECOND($E$1)))-(TIME(HOUR($D$1),MINUTE($D$1),SECOND($D$1))-TIME(HOUR(C3),MINUTE(C3),SECOND(C3)))

    AVG: 7:31
    SUM: 82:44 /3d 10h 44m

    ??

  7. Chris says:

    =(NETWORKDAYS(B3,C3)*($D$1-$E$1))
    -(TIME(HOUR(B3),MINUTE(B3),SECOND(B3))
    -TIME(HOUR($E$1),MINUTE($E$1),SECOND($E$1)))
    -(TIME(HOUR($D$1),MINUTE($D$1),SECOND($D$1))
    -TIME(HOUR(C3),MINUTE(C3),SECOND(C3)))

  8. Luke Moraga says:

    Rather than using:
    TIME(HOUR(),MINUTE(),SECOND())
    to convert time to integers and then back again to time, keep in mind that time in XL is simply stored as a decimal. You can save several steps by simply dropping the integer;
    Either:
    A2-INT(A2)
    or
    MOD(A2,1)

  9. TessaES says:

    =SUM(C3:C13-INT(C3:C13)-(B3:B13-INT(B3:B13))+(NETWORKDAYS(B3:B13;C3:C13)-1)*9/24)
    Ctrl+shift+enter gives 82 hours and 44 minutes where the 9 is the length of a workday.

  10. sixseven says:

    I get 82.73 hours.

    =(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)

  11. sixseven says:

    Whoops. Most people dont think of minutes as a decimal value.

    Properly translated, I get 82 hours and 44 minutes.

  12. Pedro Wave says:

    If start and end dates are in cells B3 and C3 respectively, this formula calculates the hours he worked for the 1st million:
    =24*SI(ENTERO(B3)=ENTERO(C3);C3-B3;C3-B3-ENTERO(C3)+ENTERO(B3)+9/24)+SI(DIAS.LAB(B3;C3)>2;(DIAS.LAB(B3;C3)-2)*9;0)

    Hours Million
    12,00 #1
    01,25 #2
    11,00 #3
    07,98 #4
    02,00 #5
    06,00 #6
    04,50 #7
    02,00 #8
    01,00 #9
    14,00 #10
    21,00 #11

    Hours he worked in total: 82,73 hours (82 hours and 44 minutes)

  13. Michael says:

    Cleanest formula I can come up with is:
    =((NETWORKDAYS(B3,C3)*(0.375))-(MOD(B3,1)-0.375)-(0.75-MOD(C3,1)))*24

    This produces 82.73 hours (82:44) with Johnny being very efficient on the 23rd and completely slacking off on his last million weaseling his way to 21 hours–lets hope we aren’t paying him hourly. :o)

  14. Abbas says:

    @ Michael. That is a great formula. Nice way to think in reverse of backing out hours from max hours. That’s neat.

  15. Michael says:

    Thank you, Abbas, much appreciated!

  16. Pedro Wave says:

    In my last message I wrote a Spanish formula. Now my English formula:
    =24*IF(INT(B3)=INT(C3);C3-B3;C3-B3-INT(C3)+INT(B3)+9/24)+IF(NETWORKDAYS(B3;C3)>2;NETWORKDAYS(B3;C3)-2)*9;0)

    Explanation for each million:
    – If start and end dates are equal, subtract them: 24*(C3-B3)
    – If start and end dates are not equal, the difference in hours is obtained and added a working day of 9 hours: 24*(C3-B3-INT(C3)+INT(B3)+9/24)
    – For each number of days greater than 2 is added one more working day of 9 hours: NETWORKDAYS(B3;C3)-2)*9
    Total hours: 82,73 hours

  17. Pedro Wave says:

    Just I changed my formula to include comma separated arguments:
    =24*IF(INT(B3)=INT(C3),C3-B3,C3-B3-INT(C3)+INT(B3)+9/24)+IF(NETWORKDAYS(B3,C3)>2,NETWORKDAYS(B3,C3)-2)*9,0)

    I hope not to have to reach one million messages!

  18. Woody says:

    this formula should work for any start date (in cell B3) and end date (in cell C3), and account for any amount of weekends between the start and end dates, to give you decimal hours taken for each million

    =((24*(C3-B3)-(((NETWORKDAYS(B3,C3))-1)*15))-(IF((C3-B3)>=7,((FLOOR(((C3-B3)/7),1)+1)*48),0)))

    where the multiplier 15 (just before the IF) is the number of hours between 6pm today and 9am tomorrow … if the work hours change, then this 15 would change accordingly

    if the difference between the start and end dates is >= 7, then a non-working weekend is involved and you have to subtract 48 hours for each weekend … the FLOOR part is used to count how many weekends are involved and multiply accordingly

    —Woody

  19. Pedro Wave says:

    The problem of writing formulas without testing them is to forget some parenthesis. My formula without known errors:
    =24*IF(INT(B3)=INT(C3),C3-B3,C3-B3-INT(C3)+INT(B3)+9/24)+IF(NETWORKDAYS(B3,C3)>2,(NETWORKDAYS(B3,C3)-2)*9,0)
    Another way to do the same:
    =IF(INT(B3)=INT(C3),C3-B3,MOD(C3,1)-MOD(B3,1)+9/24+IF(NETWORKDAYS(B3,C3)>2,(NETWORKDAYS(B3,C3)-2)*9/24,0))*24
    Total: 82.73 hours = 82:44. Always the same result!

  20. Pedro Wave says:

    As Abbas said, the Michael’s formula is great but can be simplified:
    =9*(NETWORKDAYS(B3,C3)-1)+24*(MOD(C3,1)-MOD(B3,1))
    Johnny always has worked 82.73 hours.

  21. cathleen says:

    99 hours and 45 minutes.

  22. Michael says:

    Pedro, that is a brilliant formula.

  23. Francis says:

    @ Pedro – I agree with Michael, the formula is truly a great find.

  24. Yair says:

    I used networkdays + days360 to solve the problem of calcullating through the weekend
    the catch is to put the parenthesis in the right place
    (C3-B3)*24-((NETWORKDAYS(B3,C3)-1)*15+(DAYS360(B3,C3)-(NETWORKDAYS(B3,C3)-1))*24)

  25. Jaganathan says:

    157.73 hours..!

    The real atom splitter is..
    http://www.cpearson.com/excel/DateTimeWS.htm

  26. Jaganathan says:

    OOPS..! It’d be 77.73 hrs..!

  27. Nilesh Desai says:

    Suppose I propose to get the result in column D. I will first format the column so that it has the format as DATE and type as HH:MM:SS.

    Once the above is done simply get the difference of D column – C column in D.

    Once you have copied the formula for all rows. Take the total of column D you will get the Total Hours as ;-> 250:44:00

  28. Woody says:

    correction to my earlier posting … criteria to see if a weekend is involved was wrong.

    =((24*(C3-B3)-(((NETWORKDAYS(B3,C3))-1)*15))-(IF((WEEKDAY(C3)-WEEKDAY(B3))<0,((FLOOR(((C3-B3)/7),1)+1)*48),0)))

  29. Jaganathan says:

    And, the formula is..

    =If(And(Int(StartDt)=Int(EndDt),Not(IsNA(Match(Int(StartDt),
    0,0)))),0,If(Int(StartDt)=INT(EndDt),
    0+Round(24*(EndDt-StartDt),2),
    (Max(Networkdays(StartDt+1,EndDt-1,0),0)+
    Int(24*(((EndDt-INT(EndDt))-(StartDt-Int(StartDt)))+
    0.375)/9))*8+Mod(Round(((24*(EndDt-Int(EndDt)))-9)+
    (18-(24*(StartDt-INT(StartDt)))),2),
    9)))

    May atom splitting look easier now!

  30. Jaganathan says:

    Chandoo, Let me validate my answer.
    Is 77.73 hrs correct..?

  31. Chandoo says:

    Thank you everyone for such varied, elegant and awesome solutions. I found the ones by Michael and Pedro pretty clever. But there are so many other good formulas too.

    I have prepared a solution workbook to show you how this works, You can download it here:

    http://img.chandoo.org/hw/working-hours-between-2-dates-data-solution.xls

    I will be doing a subsequent post next week explaining how you should attack this problem.

    Thank you once again.. :)

  32. Jaganathan says:

    @ Michael. Out-of-box thinking..! Quite nice!

  33. Pedro Wave says:

    @Chandoo, here is my explanation at your working hours between two dates explanation:

    Total hours worked
    =9*networkdays(start_date,end_date)-24*(start_time-9/24)-24*(18/24-end_time)
    It is the Michel’s Formula – Longer Formula:
    =networkdays(start_date,end_date)*9-24*(start_time-9/24+18/24-end_time)
    =networkdays(start_date,end_date)*9-24*start_time+(9-18)-24*end_time
    =networkdays(start_date,end_date)*9-9-24*(start_time-end_time)
    Simplifying it is Pedro’s Formula – Shorter Formula:
    =9*(networkdays(start_date,end_date)-1)-24*(start_time-end_time)

    Explanation of last formula where:
    n = networkdays(start_date,end_date)-1
    If start_date = end_date Then
    =24*(end_time-start_time)
    End If
    If start_date + n = end_date Then
    =9*n+24*(end_time-start_time)
    End If

    In these formulas are 2 cases:
    If end_time >= start_time Then
    The positive difference in hours is added to 9*n.
    End If
    If end_time < start_time Then
    The difference in hours is subtracted from 9*n.
    End If

  34. […] post builds on earlier discussion, How many hours did Johnny work? I recommend you to read that post […]

  35. Danièle says:

    No one seemed surprised at the use of the mod… so I must be missing something big!
    Is the mod used here to gather the decimal parts of the number?
    I am struggling with the equation, most probably because the mod part escapes me altogether.
    Thanks for all those pretty complicated questions that look so simple.

  36. Rajesh says:

    Hi Pedro and Chandoo,

    I have tried both your formulas and unfortunately it does not give me the right information that I need…Here is my data and the formula that I have used for them. Here is what I need…

    Work timings are : 06:30 AM till 18:30 PM from Mon – Fri
    Working hours are 12 hours which is from 06:30 AM till 18:30 PM
    Weekends are off, and holidays are off.

    AO13 (Start Date) : 2/7/14 15:44 PM; AP13 (End Date) : 2/10/14 7:16 AM

    Formula used: =(NETWORKDAYS(AO13,AP13)*12) – ((TIME(HOUR(AO13),MINUTE(AO13),SECOND(AO13))-TIME(6,30,0))*24) – ((TIME(18,30,0)-TIME(HOUR(AP13),MINUTE(AP13),SECOND(AP13)))*24)

    This gives me the answer as 3.53 which is incorrect. The correct hours worked is 3:31:49

    What am I missing from your formula ?

  37. Pedro Wave says:

    Hi Rajesh,

    The result 3.53 hours is the same as 3 hours and 56 hundredths of hour. As you want is not the decimal format but the hour format.

    3.53 hours = 3 hour + 31 minutes + 48 seconds.

    Try this in a cell: = 3.53 / 24

    And format as “Hour”

  38. Rajesh says:

    Pedro,

    Thanks. I also realized that after I posted the comment; I tried various options and what I did then was to change the format of the cell to [h]:mm:ss to display the hours which are more than 24 hours. That solved the issue. And you are correct, this formatting that you suggested also did the trick.

    Thanks for sending your comments. Really appreciate it.

    Rajesh

  39. hashmi says:

    can you please help me in getting the correct time difrence between….. incident opened date and time is 18/8/2014 8:21 am – closing time and date 20/8/2014 9:22pm working hours are 8: 30 am to 5 :00 pm for my office

Leave a Reply