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 = 11hrs12minyour help would be appreciated

✔ 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

message edited by mmcconaghy

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.

Any8: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.

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

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

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/2015In 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

message edited by mmcconaghy

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

message edited by mmcconaghy

sorry for the late response - thanks to mmcconaghy youve sloved my problem - you are a guru 10/10 mate!!

Ask Your Question

Weekly Poll

Do you think Salesforce should have bought Slack?

Discuss in The Lounge

Poll History