# 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.

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

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!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1