# Solved excel formula to calculate time worked between business hour

May 4, 2017 at 16:22:01
Specs: Windows 10
 If my work days start at 9 and finish at 5, Monday to Friday, no lunch breaks, with a total of a 40hr week, what formula would i use to produce a percentage of the week? Start and stop times will also include minutes eg cell A - 05/05/2017 08:30 cell B - 06/05/2017 11:42If i work this out manually the hours and minutes worked = 11hrs12minyour help would be appreciated

See More: excel formula to calculate time worked between business hour

May 7, 2017 at 12:03:59
 Played around with the formula a bit moreas the last one did not have a lot of error checking,This version does a bit more error checking:=IF(OR(A2="",B2=""),"",IF(AND(INT(A2)=INT(B2),NOT(ISNA(MATCH(INT(A2),\$F\$2:\$F\$5,0)))),"0 days 0 hours",IF(INT(A2)=INT(B2),"0 days "&HOUR((B2-A2))&" hours "&TEXT(MINUTE(B2-A2)/60,"0.00")&" Min ",MAX(NETWORKDAYS(A2+1,B2-1,\$F\$2:\$F\$5),0)+INT(24*(((B2-INT(B2))-(A2-INT(A2)))+(\$E\$3-\$E\$2))/(24*(\$E\$3-\$E\$2)))&" days "&INT(MOD(ROUND(((24*(B2-INT(B2)))-24*\$E\$2)+(24*\$E\$3-(24*(A2-INT(A2)))),2),ROUND((24*(\$E\$3-\$E\$2)),2)))&" hours "&TEXT(MOD(MOD(ROUND(((24*(B2-INT(B2)))-24*\$E\$2)+(24*\$E\$3-(24*(A2-INT(A2)))),2),ROUND((24*(\$E\$3-\$E\$2)),2)),1),"0.00")&" Min. ")))Again, it is very long, so best to Copy/PasteMIKEmessage edited by mmcconaghy

#1
May 4, 2017 at 18:08:04
 OK, I'm confused.1 - You say your work days start at 9, yet your example start time is 08:30.2 - You say you want a "percentage of the week" yet you gave an example answer in hours and minutes.Assuming that 11 hours 12 minutes is correct, the percentage formula would be:=(11+12/60)/403 - How did you get 11 hours 12 minutes from those start & end times? You didn't include AM or PM, but that doesn't matter. Any 8:30 to 11:42 has to be greater than 12 hours or less than 4.What am I missing?

Report •

#2
May 4, 2017 at 18:28:38
 I am also unsure of what your asking.Do you want a formula that will give you the 11 hrs 12 Min answer using your Start & End Times?orDo you want to know what the daily percentage of 11 Hrs 12 Min is, IE 11:12 divided by 5 ?The answer for the second questions is 2 Hrs 14 Min 24 SecMIKEhttp://www.skeptic.com/

Report •

#3
May 4, 2017 at 19:26:18
 Soz and Thanks guys for your lovely responses: early morning for me (need another beer)correction to my example: cell A - 05/05/2017 09:00 cell B - 06/05/2017 13:42If i work this out manually the hours and minutes worked = 12hrs42minI would like the result to be in days (whole number), Hours (whole number) and minutes as a percentage of an hour eg. 42 minutes = .70

Report •

Related Solutions

#4
May 4, 2017 at 20:36:25
 We are going to use the NETWORKDAYS function, so with your data set up like:``` A B C D E F 1) Start Date & Time End Date & Time Total Hours Holidays 2) 05/05/2017 09:00 05/06/2017 13:42 1 Days 4 Hour 0.70 Minutes Day Start Time 09:00 02/16/2015 3) Day End Time 17:00 03/25/2015 4) 07/04/2015 5) 09/17/2015 6) 12/25/2015 ```In cell C2 enter the formula:=NETWORKDAYS(A2,B2,F2:F6)&" Days "&HOUR(MOD(B2-A2,1))&" Hour "&TEXT((MINUTE(MOD(B2-A2,1))/60),"0.00")&" Minutes"See how that works for you.EDIT: Posted wrong formula, the above does not take into accountyour daily start and end times.This one should work=IF(AND(INT(A2)=INT(B2),NOT(ISNA(MATCH(INT(A2),F2:F5,0)))),"0 days 0 hours",IF(INT(A2)=INT(B2),"0 days "&ROUND(24*(B2-A2),2)&"hours",MAX(NETWORKDAYS(A2+1,B2-1,F2:F6),0)+INT(24*(((B2-INT(B2))-(A2-INT(A2)))+(E3-E2))/(24*(E3-E2)))&" days "&INT(MOD(ROUND(((24*(B2-INT(B2)))-24*E2)+(24*E3-(24*(A2-INT(A2)))),2),ROUND((24*(E3-E2)),2)))&" hours "&TEXT(MOD(MOD(ROUND(((24*(B2-INT(B2)))-24*E2)+(24*E3-(24*(A2-INT(A2)))),2),ROUND((24*(E3-E2)),2)),1),"0.00")&" Min. "))It is very long, best to Copy/Paste.Sorry for the confusion.MIKEmessage edited by mmcconaghy

Report •

#5
May 7, 2017 at 12:03:59