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:42
If i work this out manually the hours and minutes worked = 11hrs12min

your help would be appreciated


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

Report •

✔ Best Answer
May 7, 2017 at 12:03:59
Played around with the formula a bit more
as 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/Paste

MIKE

http://www.skeptic.com/

message 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)/40

3 - 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?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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?
or
Do 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 Sec

MIKE

http://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:42
If i work this out manually the hours and minutes worked = 12hrs42min
I 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 account
your 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.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#5
May 7, 2017 at 12:03:59
✔ Best Answer
Played around with the formula a bit more
as 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/Paste

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#6
May 21, 2017 at 23:06:24
sorry for the late response - thanks to mmcconaghy youve sloved my problem - you are a guru 10/10 mate!!

Report •

Ask Question