How many hours did Billy work? [Solve this]

Posted on June 5th, 2015 in Excel Challenges - 140 comments

Here is a simple but tricky problem.

Imagine you are the HR manager of a teeny-tiny manufacturing company. As your company is small, you just have one employee in the shop floor. He is Mr. Billy.

As this is a one person production facility, Billy has the flexibility to choose his working hours. At the end of each week, Bill would email you a file that says start & end times of his work. You are supposed to look at this file, figure out how many hours he worked and pay him accordingly.

So you are looking at the recent data Billy sent.

So how would you calculate the total hours?

• Note: Billy doesn’t know much about Excel.

What formula would you write in Hours worked column for each day?

Assume Start times are in C4:C9 and End times are in D4:D9

PS: In case you want a sample file to play with this data & work, download it here.

 Changing stubborn opinions with visualizations [case study] What functions is Excel missing ?
 Written by Chandoo Tags: date and time, downloads, excel formulas, homework, Learn Excel Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

140 Responses to “How many hours did Billy work? [Solve this]”

1. GDH says:

=IF(D4<C4, 1+D4-C4,D4-C4)

• alexander says:

=TEXT(D4-C4,"h")

2. Neil A says:

=IF(D4>C4,D4-C4,((D4+1)-C4))
Assumes he never works more than a 24 hour shift

3. Michael (Micky) Avidan says:

In cell F4 type: =MOD(D4-C4,1) and copy down.

In cell F11 type =SUM(F4:F9) and custom format as: [h]:mm

Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

• Ronnie says:

Micky,
I get .30 using your formula. What am I doing wrong?

• Ronnie says:

OK, if I convert this to a Time value, I get 7:15. But I don't think Hours Worked is a time value. I prefer Nick's formula with *24 added to get the numeric value of 7.25.

Thanks,
Ronnie

• Michael (Micky) Avidan says:

@Ronnie,
You are welcome to choose/use any formula you want.
I'm looking for some elegance beside using "cold" formulas.
Now, imagine a start hour: 08:07 AM. End hour: 03:50 PM.
Upon multiplying by 24 and formatting the cell as "Number" you'll get: 7.72 (while my suggestion shows: 07:43)
Please take a moment and tell me (us) what is more appropriate.
Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

• Dheeru says:

If the confusion is in AM and PM then we have to use formula as "=(D4-C4)+1" , which will reflect correct hours in time format. But if you want to convert it into number format then value will not be correct for reverse timing like "9:00 PM 5:00 AM". For that we have to use logic to convert into num format that would be "=RIGHT(F4,LEN(F4)-1)*24"

• Arun Rajappa says:

I used if(starttime>0.5,(1-Starttime)+endtime,endtime-starttime)

4. Fredy says:

I used at the end the "IF" way. One single formula that you can use all over. Thanks a lot for this web! Greetings,
Fredy

5. Fredy says:

Upps, I forgot the formula
=IF(D4>C4;D4-C4;D4+2*TIME(12;0;0)-C4)

• Fredy says:

as you can see I didn't know how to add 24 hours, that's why 2xTIME(12;0;0), jajaja. Thanks to you I know now (just add "1").

• Michael (Micky) Avidan says:

@Fredy,
As you can see I like "short formulas" (the shorter the better) - so, your approach can be shorten down to:

=D4-C4+(D4<C4)

Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

6. Modifying Micky's

In F4 =MOD(D4-C4,1)*24 copy down

=SUM(F4:F9) and format either fractions of appropriate number of decimals. 10 1/2 hours of 10.5 hours.

To run payroll I want a number of hours, not an Excel format time result - it is easier to multiply that by hourly rate.

• Michael (Micky) Avidan says:

@Nick,
I don't remember we were asked to calculate any payroll.
BUT, if we would have then, to my(!) opinion, it is more elegant to present the day by day working hours in HOURS Format and to multiply the end TOTAL by 24 and by the hour wage.

Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

7. ZUR says:

good teaser

8. Jo says:

=(D4-C4+(C4>D4))*24

• Michael (Micky) Avidan says:

@Jo,
Multiplying by 24 returns a DECIMAL Time Value which is, mainly, used when one needs to calculate wages by the hour.
In this case you may/should leave it out.
Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

9. Philip_Go says:

=TEXT(IF(D7<C7,((24-C7)+D7),C7-D7),"HH:mm")

🙂 Philip

• Michael (Micky) Avidan says:

@Philip_Go,
Chandoo's range starts at row 4 (not 7).
Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

10. chandra mohan says:

=abs(d7-c7) and change cell format to hh:mm

• chandra mohan says:

for total hours, change cell format type to [h]:mm:ss, you can apply same format to diff. of time also.

• Michael (Micky) Avidan says:

@Chandra,
To my opinion the total working hours from 09:00 PM (21:00) till 05:00 AM is: 08:00 and not 16:00.
Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

11. Darin says:

=((D4+(C4>D4)*1)-C4)*24

12. Peter W. says:

=(IF(C4<D4,0,1)+D4-C4)*24

13. Phil says:

=sum each day; then =sum total of week

14. Paul says:

=IF(HOUR(C2)-HOUR(B2)<0,24+(HOUR(C2)-HOUR(B2)),HOUR(C2)-HOUR(B2))

15. Ronnie says:

I like long, drawn-out formulas and didn't want to make the assumption that Billy would only work complete hours. So here is what I came up with. You may now start laughing.

=IF(C4<D4,(HOUR(D4)+MINUTE(D4)/60)-(HOUR(C4)+MINUTE(C4)/60),(HOUR(D4)+MINUTE(D4)/60-(HOUR(C4)+MINUTE(C4)/60)+24))

16. Michael (Micky) Avidan says:

@Ronnie,
With all due respect (I'm not laughing - even not when I see too long formulas) BUT I just would like to point out that all previous suggested formulas (that work as expected) are capable to handle fraction of hours.
Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

17. Ken Puls says:

I'd just use the following Power Query script. That way I just have to right click and refresh the output next time, and never write the formula again:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type2" = Table.TransformColumnTypes(Source,{{"Start", type datetime}, {"End", type datetime}}),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Time Subtraction",{"End_Adj"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Start", type time}, {"End", type time}})
in
#"Changed Type"

• gino says:

Ha ha ha - Ken - that's awesome!!! Gotta love it!

• Rebou says:

Oh! You clever boy, and not one to hide his light under a bushel.

18. ChacoKevy says:

I love this blog post, and am thankful to see everyone's comments. Thanks so far!

I once came upon a problem similar to this. Can we have a part 2 to this post with a little more difficulty?
For instance, how about a formula to calculate total net hours spent on a project, but using an employee's work schedule, days off, and holidays?
Assume Jim's job is restoring antique cars. His regular schedule is 09:00 to 17:00 M-F. His most recent project began on Thursday, July 3rd 2013 at 14:00 and he finished on July 14th 2013 at 11:00.
Jim works in America, where July 4th is a Holiday, and therefore doesn't work.
So if Columns A,B,C,D,E are Shift start, Shift end, Project start, Project end, Holidays in the year, row 2 reads out as 09:00, 17:00, (date submitted in US English) 7/3/2013 14:00:00, 7/14/2013 11:00:00, 7/4/2013. How many hours did Jim work?

19. Kevin says:

=IF((D5<C5),24-(C5*24)+(D5*24),(D5-C5)*24)

20. =IF(D4<C4,(TODAY()+1+D4)-(TODAY()+C4),D4-C4)*24

Handles negative hours. There is a reason why I am doing it like this - anyone hazard a guess?

21. Rich says:

Not very 'clean', in cell e4 enter =12-c4+d4 (copy down).

• Michael (Micky) Avidan says:

@Rich,
Are you, somehow, related to Billy ?
He will be very happy to get paid for 1700 Hours (which is the total sum of your daily calculation).
Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

• Rich says:

I didn't add them together to get 1700 hours - you did. I did the calculations to convert clock time into daily hours worked. From there, many of us born prior to 1985 tend to add the 6 daily numbers in our head.

22. Vaibhav Garg says:

This does work well. Just a little twist to the cut and dried. (N(NOT(D4>C4))+D4-C4)*24

• Michael (Micky) Avidan says:

@Vaibhav,
So then after eliminating unnecessary operands you'll end up with: =((D4<C4)+D4-C4)*24
Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

23. Alf says:

answer is 44, I sent the MS Excel file

24. gino says:

44 hours...

In cell E4:
=(D4-C4+(D4<C4))*24

Drag down to E9 and then =SUM(E4:E9) in cell E10 to show somebody needs to pay Mr. Billy for 44 hours work. Oh, and for the record, Mr. Billy earns \$459.78 per hour and has authorized me to collect his paycheck this week. LOL!!!

Cheers,
Gino

• Anil says:

I got it

25. Jack Surma says:

How about simply this to get the daily hours worked:

=MOD((D4-C4+1)*24,24)

It subtracts end from start and adds 1 (day) in case it is PM to AM
Then if multiplies it by 24 to get hours from the decimal days
Lastly it does a mod to "remove" the 24 hours if the values were in the same day

=+IF(C4<D4,D4-C4,D4+24-C4)

27. Nam says:

=((D4-C4)+(C4>D4))*24

28. Unimord says:

=SUM(D4,(D4<C4)*720,-C4) and copy down.

29. Rich says:

To keep transparency that we're working with time:

= IF(HOUR(D4) - HOUR(C4)>0,
HOUR(D4) - HOUR(C4),
24 + (HOUR(D4) - HOUR(C4))
)

30. N Shivkumar says:

=(IF(C4>D4,D4+1-C4,D4-C4))*24

• K-Li-Ch says:

=IFI(D4 >=C4;D4 - C4; D4 - C4 + 1) * 24

31. Dan says:

Sheet Cell Value Displayed value Formula
billy F4 0.291666667 7:00 =IF(C4="","",IF(D4="","",+D4-C4))
billy F5 0.333333333 8:00 =IF(C5="","",IF(D5="","",+D5-C5))
billy F6 0.291666667 7:00 =IF(C6="","",IF(D6="","",+D6-C6))
billy F7 0.333333333 8:00 =IF(C7="","",IF(D7="","",+D7-C7))
billy F8 0.25 6:00 =IF(C8="","",IF(D8="","",+D8-C8))
billy F9 0.333333333 8:00 =IF(C9="","",IF(D9="","",+D9-C9))
billy F11 1.833333333 44:00 =SUM(F4:F9)

32. Dan says:

=IF(C4="","",IF(D4="","",+D4-C4))
=IF(C5="","",IF(D5="","",+D5-C5))
=IF(C6="","",IF(D6="","",+D6-C6))
=IF(C7="","",IF(D7="","",+D7-C7))
=IF(C8="","",IF(D8="","",+D8-C8))
=IF(C9="","",IF(D9="","",+D9-C9))
=SUM(F4:F9)

33. N Shivkumar says:

=(IF(C4>D4,D4+1-C4,D4-C4))*24
Total 44 hours

34. Truong Nguyen says:

=IF(HOUR(D4)>12,HOUR(D4-C4),HOUR(24-C4)+HOUR(D4))

35. Don says:

=ABS(IF((D4-C4)*24>0,(D4-C4)*24,((C4-(1+D4)))*24))

• Johniron says:

=MOD(ABS(D4-C4);1/2)
and
=SUM(F4:F9)*24

36. June says:

Total 44 hours.

Format the sum cell: [h]:mm;@

37. Mark says:

I had this problem with the added twist of Before Lunch (in:C11/out:D11) and After Lunch (in:E11/out:F11) to calculate Regular (max 8 hours) and OT over 8 hours. Times are entered as AM / PM.

Regular: =IF((((D11-C11)+(F11-E11))*24)>8,8,((D11-C11)+(F11-E11))*24)

OT: =IF(((D11-C11)+(F11-E11))*24>8,((D11-C11)+(F11-E11))*24-8-L11,0)

38. Jeff says:

Oh, you non-HR folks; Billy does not get paid for his meal break each shift. Need to subtract one hour per worked shift greater than 6 hours, no deduction for shorter shifts as no meal break is required. 😉

• Michael (Micky) Avidan says:

@Jeff,
Most of the time I take people as serious even if the put a blinking smiley at the end of their post - therefore, here (in the linked picture) is my suggestion to the above mentioned situation.
Pls note that the Threshold & Lunch break time were entered in 2 cells in order to make it easy to change them if needed.

http://jpg.co.il/view/5572a6ea0abbd.png/

Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

• Jeff says:

Very elegant solution. Thanks.
Brought a whole new level of spreadsheets top my attention!

Jeff

• Eddie says:

Jeff, this should answer your problems (N.B. it's 1 meal break for every 6 hours worked)
F4: Hours worked =(D4<C4)+(D4-C4)
G4: # Meal Breaks =INT(F4/TIME(6,0,1)) [The extra second is there to ensure the shift is over 6 hours and not equal to 6 hours]
H4: Paid Hours =F4-TIME(G4,0,0)
H11: =SUM(H4:H9)

gives you 39 paid hours

39. W.Carr says:

=IF(AND(C4*24>12,D4*24>12),((D4-C4)*24),IF(C4*24>12,24-(C4*24)+(D4*24),((D4-C4)*24)))

40. Somak says:

=IFERROR(HOUR(D4-C4),24-HOUR(C4-D4))

41. Vikram says:

=IFERROR(HOUR(D4-C4),HOUR(12-C4)+HOUR(D4-0)). Total 44 hours

42. Ron Wallace says:

This is the best I can find.

Enter =MOD(24*(1+D4-C4),24) in cell F4 and copy down.

43. Gweed says:

F cells format : Numeric
in F cells
=IF((HOUR(D4)-HOUR(C4))>0,HOUR(D4)-HOUR(C4),24+HOUR(D4)-HOUR(C4))

44. Brett Ellingson says:

I thought my answer was cool...untill I looked at a few of these answers 🙂

This gives me the number of hours he worked that day
IF(TEXT(D4,"[m]")*1>TEXT(C4,"[m]")*1,TEXT(D4,"[m]")-TEXT(C4,"[m]"),1440+TEXT(D4,"[m]")-TEXT(C4,"[m]"))/60

converted each time entry to minutes (TEXT function) - I don't need the TEXT function but wanted to put it in there anyway to show my process

subtracted start time from finish time
used IF function to look for when end time was in the early AM hours
Multiplied by 1 to convert text back to number with IF function

45. Chuck says:

=IF(D4>C4,(D4-C4)*24,((D4+1)-C4)*24)

46. Eljefegeneo says:

I got it, but I am not sure if there is an error in my formula:

=(D4-C4) +24

47. John D says:

For each day =(C2-B2)*24
Sum all days.
total = 44 Hrs

48. Bala says:

Easy..
IF((D4-C4)>0,(D4-C4)*24,(1-(C4-D4))*24)

49. Shajid Hossain says:

For a day =IF(B2>C2,1+C2,C2)-B2
For Total =SUM(E2:E7)*24

50. Gaurav says:

=IF(D9<C9,D9+1,D9)-C9

51. Amit says:

Used below formula to get the result

=IF(TEXT(D4,"HH:MM")>TEXT(C4,"HH:MM"),TEXT(D4,"HH:MM")-TEXT(C4,"HH:MM"),(24-TEXT(C4,"HH:MM"))+TEXT(D4,"HH:MM"))

52. jraju says:

Hi, My simple formula would be
=TEXT(d4-c2,"h"), provided the input entries in c and d coloum are manually formatted as 1:30 PM in the time format to get the exact hours.

53. Deepak Kumar says:

=IF(E4<C4,TEXT(24+D4-C4,"hh:mm"),TEXT(D4-C4,"hh:mm"))

54. Rahat Khan says:

=24-(C4-\$H\$4)-(\$H\$4-D4)

• Rahat Khan says:

particialy work 🙁

• Michael (Micky) Avidan says:

@Rahat,
Let us assume that Billy gets \$20 per hour.
After calculating his daily hours - will you, please, sum his 6 days working hours and multiply that sum by \$20 ?
How much did you get ?
From previous posts it is easy to find out that the total hours were 44 and by multiplying them by \$20, Billy should be paid \$880.

Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

This formula should be applied in above condition.

=IF(D4>C4,TEXT(D4-C4,"h"),TEXT(C4-D4,"h"))

Check and reply me either it is easy to understand and apply

56. Hasaan Fazal says:

=D4-C4+(C4>D4)
Problem is that time can't be negative so one way to get around it is use logical test to make it positive.

Would like to hear from other excel heads here to see if this approach stands the test.

Hasaan

57. David Hager says:

=MOD(D4+1-C4,1)*24

58. Taanya Lynn Pillsbury says:

=IF(D4<C4,D4+1,D4)-C4 , this calculate the proper hours for each day worked; however, it doesn't give the correct sum at the bottom if you try to add the hours up. But, if you change the grand total hours to a custom format of [h]:mm:ss then the total hours is correct (44 hours).

59. Xiq says:

One formula non-array solution:
=SUMPRODUCT((D4:D9-C4:C9+(C4:C9>D4:D9)))*24

• Xiq says:

Another one formula non-array solution:
=SUMPRODUCT(MOD(D4:D9+1-C4:C9,1))*24

• Michael (Micky) Avidan says:

@Xiq,
What role plays the "+1" ?
Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

60. IndDon says:

One of the ninja's helped me with the below formula, added function was ABS. You have to format the Hours worked column to 00.00 :

1. Hours worked=IF(OR(\$C4="",\$D4=""),"",ABS(MOD(\$D4-\$C4,1)*24))
2. Total=SUM(F4:F9)

61. Shaji says:

format the 'Hours Worked' column as number with 2 digits and use the following formula...

=HOUR(IF(C4>D4,(D4+24)-C4,D4-C4))+(MINUTE(IF(C4>D4,(D4+24)-C4,D4-C4))/60)

62. Ateeque Malik says:

=IF((D4-C4)<0,((D4-C4)+1)*24,(D4-C4)*24)

• Ateeque Malik says:

And format the cells as General.

63. Michael (Micky) Avidan says:

@To whom it may concern !

The shortest formula (for daily hours calculation) is: =D4-C4+(D4<=C4) and NOT: =D4-C4+(D4<C4).

The simple reason (you may bump into it in extreme cases) is that there may be a 24:00 shift (Start: 09:30 AM, End: 09:30 AM (on the next day) and the lack of a "equal sign" will show to 00:00 hours instead of 24:00.
(With all due respect and as a Proper Disclosure I didn't checked/followed all suggestions - especially not the long formulas.

Take care and keep on Excelling..."
Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

• Bill Szysz says:

Maybe Billy goes in and goes out in the same minute?
We can not be sure, which case is, so MOD(End-Start,1) is as good as End-Start+(End<=Start). Only if we exclude situation when Billy goes in and goes out in the same minute we can say that was 24 hours shift. If not, we don't know it was zero hours shift or 24 hours shift. 🙂

Regards 🙂

64. MF says:

Is =MOD(End-Start,1) the simplest one to get the difference?

And multiplying the result by 24 to get the result in hours.

• Michael (Micky) Avidan says:

@MF,
Yes it is - with an exceptional case like a 24 hours shift (09:30 AM -> 09:30 AM)
In such a case that formula returns 00:00 (0) instead of 24:00 (1).
The SHORTEST & CORRECT formula is: =End-Start+(End<=Start)
Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

• MF says:

Hi Micky,
You get the point. I didn't expect a long working day of 24 hours... ;p
I like the use of the +(End<=Start). Very nice.
Cheers,

65. Phoy says:

It should be
=IFERROR(HOUR(D4-C4),HOUR(C4-D4)-24)

Sum = SUM(F4:F9)

You get total hours = 44 hours

• aakash says:

=IFERROR(HOUR(D4-C4),HOUR(24-(C4-D4)))

a minor tweak!

66. rop says:

HOUR(D4)-HOUR(C4)+24*(C4>D4)

Sum = SUM(F4:F9)

67. Krishna says:

=IF(C4<D4,HOUR(D4)-HOUR(C4),24-ABS((HOUR(D4)-HOUR(C4))))

68. Andy Forrester says:

=IF(D4>C4, (D4-C4)*24,24 +(HOUR("12:00 AM")-HOUR(C4))+HOUR(D4))

69. bVs says:

for the cells
"=IF((HOUR(D4)-HOUR(C4))>0,HOUR(D4)-HOUR(C4),HOUR(D4)-HOUR(C4)--24)"

then a simple sum of all the cells for the total

70. Jacinto Morales says:

You need to be present if you start at night to next day.
=IF(d4<c4;D4+1;D4)-C4

TIME IN TIME OUT HOUR
11.00 PM 07.00 AM 8.00
10.00 AM 03.00 PM 5.00

71. Chirayu says:

=IFERROR(HOUR(D4-C4),HOUR(C4-D4)/2)

• Chirayu says:

just checked, doesn't work 😛 back to drawing board

• Chirayu says:

Fixed it
=IF(HOUR(D4)-HOUR(C4)<0,(24-HOUR(C4))+HOUR(D4),HOUR(D4)-HOUR(C4))

72. I have a question... probably trivial, but if Billy worked 10am Tuesday to 6pm Wednesday, how could he start Wednesday's shift at 3pm?

Wed - Thu has a similar overlap...

73. Peter Allen says:

I know I'm slow with this, but:
=IF(D4<C4,D4+12,D4)-C4
does the trick by adding 12 hours to an all nighter.

74. OK after making that dumb mistake... how about filling this formula down from F4:

=24*(D4+(C4>D4)-C4)

Then F11:

=SUM(F4:F9)

It's neat.

75. Sérgio Silva says:

hi.
in this cases, i usually use:

i'm from Portugal, so
=RESTO(D4-C4;1)

that in english, is

=Mod(D4-C4,1)

76. Bruno says:

=IF(D4-C4)>0;D4-C4;(12-C4)+D4)

77. Ganesh says:

=IF(C2>B2,TEXT(C2-B2,"H"),24-TEXT(B2-C2,"H"))

78. Carpy1985 says:

I used:

=IF(HOUR(D4)-HOUR(C4)<0,(24-HOUR(C4))+HOUR(D4),HOUR(D4)-HOUR(C4))

which spat the answer out in number not time format.

I have never used the MOD function so thats something to learn next 🙂

Formula in cell F4:
=IF(D4>C4;D4-C4;(1-C4)+D4)

80. This is a fun puzzle. Thanks Chandoo!
I used a single cell array formula:
=SUM(IF(\$D\$4:\$D\$9>\$C\$4:\$C\$9,\$D\$4:\$D\$9-\$C\$4:\$C\$9,\$D\$4:\$D\$9+(1-\$C\$4:\$C\$9)))
(don't forget to hold down the 'Ctrl' key and the 'Shift' key while you press the 'Enter' key as this is an array formula).

I just noticed that Xiq has a Sumproduct. Nice!
Cheers,
Kevin Lehrbass

81. JHarris says:

Subtracting End Time from Start Time works fine as long as both occur on the same day. If one, for ex, starts at 11:00 pm and ends at 5:00 am, straight subtraction yields a negative number. To fix, I used an if statement: if end - start is +, then just subtract end from start, else add 24 to (end - start) to get the correct figure. In each case multiplying by 24 (*24) transforms the answer from decimal days to hours. I did not format further as I would hours in decimal form to enable further pay calculations.

=IF(D7-C7>0,(D7-C7)*24,24+((D7-C7)*24))

Another good one Chandoo, when is your next dashboard contest???

82. Subbu says:

Hello,

Its working for me..
=IF(D4>C4,D4-C4,1-(C4-D4))

83. Brij Arora says:

=IF((D4-C4)>0,(D4-C4)*24,((D4*24)+24-(C4*24)))

number format
Hours worked total 44

84. Anant Jain says:

=IF(AND(HOUR(A2)>12,HOUR(B2)<12),24-(HOUR(A2)-HOUR(B2)),HOUR(B2)-HOUR(A2))

85. jay prakash says:

=IF(C4<D4,D4-C4, C4-D4), do it for all cells and then sum , will provide the hours.

Only change the answer cell in format of h:mm:ss

Done it and found OK

86. jay prakash says:

Total Hours is 72 Hrs..

87. Hi Everyone,

I created a YouTube video to explain my array solution:
https://youtu.be/0D98I73TJeI

Cheers,
Kevin Lehrbass

88. Michael (Micky) Avidan says:

@Kevin,
While your suggested formula works - try a much shorter Array solution: =SUM(D4:D9-C4:C9+(D4:D9<=C4:C9))
BTW: upon presenting a single formula - all \$\$\$ signs are useless.
Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

89. Uejsi says:

1. I have used this formula to calculate each row
=IF(D4>"12:00",D4-C4,12-C4+0+D4)
2. i have formatted the result column hh:mm:ss
3. the end i have used only sum function

• Uejsi says:

this is the exact formula for each row
=NUMBERVALUE(TEXT(IF(D4>"12:00",D4-C4,12-C4+0+D4),"h"),1)
the result cell has normal sum function in total 44 hours

thnx

90. excel says:

I hope this formula will work
=HOUR(D4+24-C4)
and then doing the sum

91. Sri Vidya says:

Simplest of all solutions -
=12-C4+D4 🙂

• Michael (Micky) Avidan says:

@Sri,
Most of the above participants got a total sum of 44 hours and if Billy's hour rate is \$20 he will be paid \$880.
Would you be so kind to check if your sum comes out 44 hours - and if so, to present us the detailed calculation regarding the payment ?
Thanks,
Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

92. Luís Pires says:

=+MOD(+D3-C3;1)*24 and copy the formula down

44 Hours too...

93. Micheal Poh says:

=D4+12-C4
=D5+12-C5
=D6+12-C6
=D7+12-C7
=D8+12-C8
=D9+12-C9

i used the formula above and change the cells format to Custom H(Hours).
But i cannot get the sum of the total of Mr Willy.

94. Vijay Dhangar says:

what if there are more than 1 person??

95. […] In actualizarea saptamanala, am dat peste o provocare Excel care la prima vedere parea foarte simpla. (How many hours did Billy work?) […]

96. Zak says:

Hi,

here is mine :

=IF(D4<C4,(D4+1)-C4,D4-C4)*24 (general format)

total 44 hours

97. Marydas Joseph says:

=IF(HOUR(D4)<HOUR(C4),(HOUR(D4)-HOUR(C4))+25,(HOUR(D4)-HOUR(C4)+1))

 Changing stubborn opinions with visualizations [case study] What functions is Excel missing ?