Menu
How many hours did Billy work? [Solve this]
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.
Go ahead and answer the question.
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
Please post your answers in comments section. Billy is waiting for his pay!!!
PS: In case you want a sample file to play with this data & work, download it here.
Hello Awesome...
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.
Related articles:
|
Leave a Reply
| « Changing stubborn opinions with visualizations [case study] | What functions is Excel missing ? » |
At Chandoo.org, I have one goal, "to make you awesome in Excel & Power BI". I started this website in 2007 and today it has 1,000+ articles and tutorials on data analysis, visualization, reporting and automation using Excel and Power BI. 
140 Responses to “How many hours did Billy work? [Solve this]”
=IF(D4<C4, 1+D4-C4,D4-C4)
=TEXT(D4-C4,"h")
=IF(D4>C4,D4-C4,((D4+1)-C4))
Assumes he never works more than a 24 hour shift
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
Micky,
I get .30 using your formula. What am I doing wrong?
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
@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
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"
I used if(starttime>0.5,(1-Starttime)+endtime,endtime-starttime)
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
Upps, I forgot the formula
=IF(D4>C4;D4-C4;D4+2*TIME(12;0;0)-C4)
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").
@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
cool!
"and pay him accordingly."
=(IF(C4>D4,D4+1-C4,D4-C4))*24
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.
@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
good teaser
=(D4-C4+(C4>D4))*24
@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
=TEXT(IF(D7<C7,((24-C7)+D7),C7-D7),"HH:mm")
🙂 Philip
@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
=abs(d7-c7) and change cell format to hh:mm
for total hours, change cell format type to [h]:mm:ss, you can apply same format to diff. of time also.
@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
=((D4+(C4>D4)*1)-C4)*24
=(IF(C4<D4,0,1)+D4-C4)*24
=sum each day; then =sum total of week
=IF(HOUR(C2)-HOUR(B2)<0,24+(HOUR(C2)-HOUR(B2)),HOUR(C2)-HOUR(B2))
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))
@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
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}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type2", "End_Adj", each if [Start]>[End] then Date.AddDays([End],1) else [End]),
#"Inserted Time Subtraction" = Table.AddColumn(#"Added Custom1", "Hours Worked", each [End_Adj] - [Start], type duration),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Time Subtraction",{"End_Adj"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Start", type time}, {"End", type time}})
in
#"Changed Type"
Ha ha ha - Ken - that's awesome!!! Gotta love it!
Oh! You clever boy, and not one to hide his light under a bushel.
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?
=IF((D5<C5),24-(C5*24)+(D5*24),(D5-C5)*24)
=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?
Not very 'clean', in cell e4 enter =12-c4+d4 (copy down).
@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
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.
This does work well. Just a little twist to the cut and dried. (N(NOT(D4>C4))+D4-C4)*24
@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
answer is 44, I sent the MS Excel file
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
I got it
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)
=((D4-C4)+(C4>D4))*24
=SUM(D4,(D4<C4)*720,-C4) and copy down.
To keep transparency that we're working with time:
= IF(HOUR(D4) - HOUR(C4)>0,
HOUR(D4) - HOUR(C4),
24 + (HOUR(D4) - HOUR(C4))
)
=(IF(C4>D4,D4+1-C4,D4-C4))*24
=IFI(D4 >=C4;D4 - C4; D4 - C4 + 1) * 24
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)
=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)
=(IF(C4>D4,D4+1-C4,D4-C4))*24
Total 44 hours
=IF(HOUR(D4)>12,HOUR(D4-C4),HOUR(24-C4)+HOUR(D4))
=ABS(IF((D4-C4)*24>0,(D4-C4)*24,((C4-(1+D4)))*24))
=MOD(ABS(D4-C4);1/2)
and
=SUM(F4:F9)*24
Total 44 hours.
Format the sum cell: [h]:mm;@
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)
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. 😉
@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
Very elegant solution. Thanks.
Brought a whole new level of spreadsheets top my attention!
Jeff
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
=IF(AND(C4*24>12,D4*24>12),((D4-C4)*24),IF(C4*24>12,24-(C4*24)+(D4*24),((D4-C4)*24)))
=IFERROR(HOUR(D4-C4),24-HOUR(C4-D4))
=IFERROR(HOUR(D4-C4),HOUR(12-C4)+HOUR(D4-0)). Total 44 hours
This is the best I can find.
Enter =MOD(24*(1+D4-C4),24) in cell F4 and copy down.
F cells format : Numeric
in F cells
=IF((HOUR(D4)-HOUR(C4))>0,HOUR(D4)-HOUR(C4),24+HOUR(D4)-HOUR(C4))
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
=IF(D4>C4,(D4-C4)*24,((D4+1)-C4)*24)
I got it, but I am not sure if there is an error in my formula:
=(D4-C4) +24
For each day =(C2-B2)*24
Sum all days.
total = 44 Hrs
Easy..
IF((D4-C4)>0,(D4-C4)*24,(1-(C4-D4))*24)
For a day =IF(B2>C2,1+C2,C2)-B2
For Total =SUM(E2:E7)*24
=IF(D9<C9,D9+1,D9)-C9
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"))
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.
=IF(E4<C4,TEXT(24+D4-C4,"hh:mm"),TEXT(D4-C4,"hh:mm"))
=24-(C4-$H$4)-($H$4-D4)
particialy work 🙁
@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
=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
=MOD(D4+1-C4,1)*24
=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).
One formula non-array solution:
=SUMPRODUCT((D4:D9-C4:C9+(C4:C9>D4:D9)))*24
Another one formula non-array solution:
=SUMPRODUCT(MOD(D4:D9+1-C4:C9,1))*24
@Xiq,
What role plays the "+1" ?
Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL
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)
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)
=IF((D4-C4)<0,((D4-C4)+1)*24,(D4-C4)*24)
And format the cells as General.
@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
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 🙂
Is =MOD(End-Start,1) the simplest one to get the difference?
And multiplying the result by 24 to get the result in hours.
@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
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,
It should be
=IFERROR(HOUR(D4-C4),HOUR(C4-D4)-24)
Sum = SUM(F4:F9)
You get total hours = 44 hours
=IFERROR(HOUR(D4-C4),HOUR(24-(C4-D4)))
a minor tweak!
HOUR(D4)-HOUR(C4)+24*(C4>D4)
Sum = SUM(F4:F9)
=IF(C4<D4,HOUR(D4)-HOUR(C4),24-ABS((HOUR(D4)-HOUR(C4))))
=IF(D4>C4, (D4-C4)*24,24 +(HOUR("12:00 AM")-HOUR(C4))+HOUR(D4))
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
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
=IFERROR(HOUR(D4-C4),HOUR(C4-D4)/2)
just checked, doesn't work 😛 back to drawing board
Fixed it
=IF(HOUR(D4)-HOUR(C4)<0,(24-HOUR(C4))+HOUR(D4),HOUR(D4)-HOUR(C4))
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...
Oh my head. Sorry sorry that doesn't make sense! Eyes got screwed up lol.
Ignore please.
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.
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.
hi.
in this cases, i usually use:
i'm from Portugal, so
=RESTO(D4-C4;1)
that in english, is
=Mod(D4-C4,1)
=IF(D4-C4)>0;D4-C4;(12-C4)+D4)
=IF(C2>B2,TEXT(C2-B2,"H"),24-TEXT(B2-C2,"H"))
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)
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
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???
Hello,
Its working for me..
=IF(D4>C4,D4-C4,1-(C4-D4))
=IF((D4-C4)>0,(D4-C4)*24,((D4*24)+24-(C4*24)))
number format
Hours worked total 44
Answer is
=IF(AND(HOUR(A2)>12,HOUR(B2)<12),24-(HOUR(A2)-HOUR(B2)),HOUR(B2)-HOUR(A2))
=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
Total Hours is 72 Hrs..
Hi Everyone,
I created a YouTube video to explain my array solution:
https://youtu.be/0D98I73TJeI
Cheers,
Kevin Lehrbass
@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
Good point. Thanks Michael.
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
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
I hope this formula will work
=HOUR(D4+24-C4)
and then doing the sum
Simplest of all solutions -
=12-C4+D4 🙂
@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
=+MOD(+D3-C3;1)*24 and copy the formula down
44 Hours too...
=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.
what if there are more than 1 person??
[…] In actualizarea saptamanala, am dat peste o provocare Excel care la prima vedere parea foarte simpla. (How many hours did Billy work?) […]
Hi,
here is mine :
=IF(D4<C4,(D4+1)-C4,D4-C4)*24 (general format)
total 44 hours
=IF(HOUR(D4)<HOUR(C4),(HOUR(D4)-HOUR(C4))+25,(HOUR(D4)-HOUR(C4)+1))