Elapse time based on work hour less weekend and holidays

**russellj** · 06-07-2013, 02:13 PM

Frustrated with networkdays and time formatting - Need a formula that will give me the below result.

I'm looking for an elapsed time that will look at work hours (8a-5p), less weekends, less holidays

Start Date/Time Same Cell
05/11/2013 08:00:00

End Date/Time Same Cell
05/15/2013 15:35:00

Result Needed (day:hour:min - I dont need the result to have text because will need to average these results)
2:7:35

For some reason I can't get the day hour minute to all calculate correctly using a single cell formula. Appreciate any assistance you can provide!

**Tsjallie** · 06-07-2013, 03:30 PM

Did you try to use the WorkDay_Intl function?

**russellj** · 06-07-2013, 03:57 PM

I haven't tried any of the intl functions assuming regular workdays, network days, time would have worked... But obviously I'm still struggling with.

Do you have suggestion on setting it up?

**Kevin UK** · 06-07-2013, 06:12 PM

Hi russellj

Try the following formula and assuming: A2= Start date/time & B2=End time/date

Formula:

Please Login or Register  to view this content.

And format the cells like "d"d" hh:mm"(without the quotes or however you wish.

**russellj** · 06-10-2013, 02:33 PM

The results arent quite what I was looking for

. 5/10/13 11:55 AM 5/17/13 4:59 PM = 2:2:04 (Day Hour Min)

Based on the above Dates and times I would need the results to equal 4:14:04

. From 5/10/13 at 11:55 AM to 5PM (work hour)is 5hr 5min
. From 5/17/13 from 8AM (work hour) to 4:59PM is 8hr 59min
. From 5/10/13-5/17/13 not counting weekend is 4 days

**Kevin UK** · 06-10-2013, 06:44 PM

Hi

Ignore the cell formatting in post #4, I don't know what I was thinking! Use [h]:mm
For decimal time: =NETWORKDAYS(A2,B2)-1+(MOD(B2,1)-MOD(A2,1))*24/9
Or probably use an IF formula as excel presumes rightly that there are24hrsin a day.

**russellj** · 06-21-2013, 07:04 PM

Didnt work as intended. It might be the way I explained it so let me see if I can do a better job of detailing the problem.

Elapse time for me has a few variables to consider -

First, I only want to calculate the amount of time within the hours of 8am to 5pm

Second, I want the number of days and hours calculated based on a 9 hour work day

Third, I want to exclude weekends,holidays

Lastly, the first and last day are not counted as full days but as an amount of time between "start time" to "5pm" and "8am" to "end time"

Therefore if I have something like
start time 5/31/13 9:00AM
end time 6/5/13 11:30AM

The result should equal 3:02:30

I've tried a million (wrong) formulas and struggling.

**daddylonglegs** · 06-23-2013, 06:29 AM

Hello russellj,

It's possible to get results like that but there are a couple of problems.....

This formula will give you 3:02:30 for your example

=NETWORKDAYS(A2,B2)-1-(MOD(B2,1)<MOD(A2,1))&TEXT(B2-A2,":hh:mm")

Note: I'm assuming that start and end date/times will always fall within working hours, if not that won't always work

The problem with the above is that it's a text result and you say you want to average the results. Averaging text values won't be easy and even if the above result could be made into a numeric value that won't help you because Excel will consider the "day" part as 24 hours not 9 so when you average you'd get the wrong result, e.g. the average of 3:02:30 and 2:06:30 should be exactly 3 days because the sum of those 2 is 6 days.....but if excel averages those values it will give you 2:16:30.

I'd advise you to use the first formula suggested by Kevin and custom format the result as [h]:mm (must include square brackets) to get total elapsed hours which will be an unambiguous result, e.g. for your last example you'd get 29:30. If you want you could use my suggested formula to display as 3:02:30 but use Kevin's formula to get the figure to use in an average.

Note: you also say that 5/10/13 11:55 AM to 5/17/13 4:59 PM should be 4:14:04.......but surely if the "day" unit represents 9 hours then the hours/minutes can't exceed 8:59, wouldn't the correct result be 5:05:04 (that's what my formula will give you)