+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    Registered User
    Join Date
    04-19-2011
    Location
    Columbus, OH US
    MS-Off Ver
    Excel 2003
    Posts
    3

    Question 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. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    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.....
    Audere est facere

  3. #3
    Registered User
    Join Date
    04-19-2011
    Location
    Columbus, OH US
    MS-Off Ver
    Excel 2003
    Posts
    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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