Formulas for Elapsed Time - Non Standard Hours

Hi,

Awhile back I was here asking questions about a formula calculate elapsed time between 2 dates, but that did not count hours overnight. There was a helpful response from daddylonglegs as follows :

If you have start time and date in A2, end time and date in B2 and your daily start time (08:00) in F2 and daily end time (21:00) in F3 you can use this formula :
=(INT(B2)-INT(A2))*($F$3-$F$2)+MEDIAN(MOD(B2,1),$F$2,$F$3)-MEDIAN(MOD(A2,1),$F$2,$F$3)

Because I do not know enough about Excel I do not know how (or if it's possible) to change this formula for other scenarios.

For example, a service calendar (clock) that was active Mon-Fri 7am-7pm
or Mon-Fri 24 Hours.

Any clues? Or do I need different formulas altogether?

The reporting I do could incorporate all these circumstances in one report, and frankly, it's doing my head in.

Thanks
Narelle.

If I gave you Excel macro code for a user defined function, that you would be able to include in your worksheet formulas, would you be able to install it and work with it?

Unfortunately, the computations for what you want to do for weekly timekeeping seem too complicated to put reliably into a single worksheet formula.

Hi Frank,

Possibly, I have worked with macro code before, but in a limited capacity. I'm certainly keen to give it a try! As long as it is not too time consuming for you to write?

Thank you for the quick reply, your help is appreciated.


narelle

You can use NETWORKDAYS function to exclude weekends. Note NETWORKDAYS is part of Analysis ToolPak add-in. If you don't have this installed you may be able to install with Tools > add-ins > tick "Analysis ToolPak" box.

Assuming the following:

You have start time and date together in one cell - A2
You have end time and date together in one cell - B2

To calculate elapsed time 7:00 AM to 7:00 PM Mon-Fri Use the formula

=NETWORKDAYS(A2,B2)/2-1/2+MOD(B2,1)-MOD(A2,1)

format result cell as [h]:mm

for all time Mon-Fri

=NETWORKDAYS(A2,B2)-1+MOD(B2,1)-MOD(A2,1)

again format result cell as [h]:mm

Note: these formulas only work assuming that start time and end times will always be with the working hrs, i.e. for formula 1 A2 and B2 must be between 7 AM and 7 PM Mon-Fri otherwise you may get an incorrcet result. For formula 2 A2 and B2 can be any times Mon-Fri.

Formulas can be adapted to cope with start and end times outside these periods and also holidays can be excluded from the count if you have a list of holiday dates

Hi, Thanks for continuing to assist Daddylonglegs.

I have got a formula that uses that function as follows :

=SUM(19/24-MOD(C2,1),MOD(W2,1)-7/24)+(NETWORKDAYS(C2,W2)-2)*12/24

for a 7am-7pm M-F clock, but you are right about it slipping up when a service request is logged outside of these hours. Or even finishing outside of these hours, I think it falters there as well? How can this be amended?

I was also not sure how to adapt it to be say 5 days a week but 24 hours clock. Would the 19 become 24 and the 12 become 0?

Rgds
Narelle

When you have start and end times that can be at any time use this formula to count MF hours between 7 AM and 7 PM

=(NETWORKDAYS(C2,W2)-1)/2+IF(NETWORKDAYS(W2,W2),MEDIAN(MOD(W2,1),7/24,19/24),19/24)-MEDIAN(NETWORKDAYS(C2,C2)*MOD(C2,1),7/24,19/24)

for 24 hours Monday to Friday

=NETWORKDAYS(C2,W2)-1+IF(NETWORKDAYS(W2,W2),MOD(W2,1),1)-IF(NETWORKDAYS(C2,C2),MOD(C2,1),0)

in both cases format result cell as [h]:mm

at first glance (have tested on only a couple of examples), this appears to be working well. excellento!

thanks so much. wish i could figure this stuff out myself, I'll have a closer look at these formulas now that i have a few different scenarios to study.

thanks!