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.

## Bookmarks