# Trying to understand a formula to calculate elapsed time in a specific time period

1. ## Trying to understand a formula to calculate elapsed time in a specific time period

My true goal is this: given a column of start date/times (D) and a column of end date/times (E), determine the elapsed time for each row, and then an average of the elapsed time.

The gotchas here is that I only wanted to calculate elapsed time within working hours.

In searching around the net, I came across an article <http://www.eggheadcafe.com/software/...and-hours.aspx > which recommended this formula:
=(NETWORKDAYS(D1,E1)-1)*5/12+IF(NETWORKDAYS(E1,E1),MEDIAN(MOD(E1,1),3/4,1/3),3/4)-MEDIAN(NETWORKDAYS(D1,D1)*MOD(D1,1),3/4,1/3)

The writer says that the formula "...will calculate weekday hours between 08:00 and 18:00
...[either date/time column] may be at weekends or evenings "

I understand the function of networkdays. I do not understand the use of the median and mod functions here.

I'd like to understand how the formula works, so that I could, perhaps, tweak it for a different time range.  Register To Reply

2. ## Re: Trying to understand a formula to calculate elapsed time in a specific time perio

Hello Ivirden, that looks like one of mine....

In that formula the fractional amounts represent times, 3/4=18:00, 1/3 = 08:00 and 5/12 represents the length of the working day 18:00-8:00=10:00.

It's perhaps easier (and more flexible) if you put the day start (08:00) and day end (18:00) in two cells and reference those, e.g. using J2 and J3 that would be:

=(NETWORKDAYS(D1,E1)-1)*(J\$3-J\$2)+IF(NETWORKDAYS(E1,E1),MEDIAN(MOD(E1,1),J\$3,J\$2),J\$3)-MEDIAN(NETWORKDAYS(D1,D1)*MOD(D1,1),J\$3,J\$2)

This particular formula is more complex because it still calculates correctly if the start or end times are outside the working hours, e.g. if start time might be 06:00 or end time on a Saturday.

If your start and end times will always be between J2 and J3 times (on Monday to Friday) then this formula will be sufficient:

=(NETWORKDAYS(D1,E1)-1)*(J\$3-J\$2)+MOD(E1,1)-MOD(D1,1)

Both formulas essentially count the number of working days and multiply that by the number of hours in a working day - and then make an adjustment for the start and end times, with the former that's clearly more complex........

...I can give you more detail if you want.....  Register To Reply

3. ## Re: Trying to understand a formula to calculate elapsed time in a specific time perio

Thank you for your explanation. I appreciate the help in tweaking the formula!  Register To Reply