Results 1 to 3 of 3

Compute the total elapsed working time

Threaded View

  1. #1
    Registered User
    Join Date
    04-08-2014
    Location
    Palm Bay, FL
    MS-Off Ver
    Excel 2010
    Posts
    1

    Compute the total elapsed working time

    I have searched high and low for a formula that would compute the total elapsed working time (in minutes) of an event taking into consideration only the working days and specified working hours. I finally broke down and wrote one myself. Here it is ... enjoy!

    Named Ranges:
    • DayStart - time of day business starts
      DayEnd - time of day business ends
      HolidayList - vector of holiday dates
      StartEvent - the date/time of the start of the event
      EndEvent - the date/time of the end of the event

    Formula:
    Formula: copy to clipboard
    =(IF((INT(StartEvent)=INT(EndEvent)),IF(StartEvent-INT(StartEvent)<DayEnd,(MIN(EndEvent-INT(EndEvent),DayEnd)-MAX(StartEvent-INT(StartEvent),DayStart)),0),IF(StartEvent-INT(StartEvent)<DayEnd,DayEnd-(StartEvent-INT(StartEvent)),0))*NETWORKDAYS(StartEvent,StartEvent,HolidayList)+(IF(NETWORKDAYS(StartEvent,EndEvent,HolidayList)>2,(NETWORKDAYS(StartEvent,EndEvent,HolidayList)-2)*(DayEnd-DayStart)))+IF(INT(EndEvent)>INT(StartEvent),IF(AND(EndEvent-INT(EndEvent)>DayStart,NETWORKDAYS(EndEvent,EndEvent,HolidayList)=1),(MIN(EndEvent-INT(EndEvent),DayEnd)-DayStart),0),0))*60*24


    How it works?
    Although it appears a little complicated, it basically is:
    The number of minutes in the first day of the event (as long as it is a working day)
    PLUS
    The number of minutes of the last day of the event (also provided it is a working day)
    PLUS
    Number working days between the first day and last day * working minutes in a day
    Attached Files Attached Files
    Last edited by rrlarson; 04-08-2014 at 04:26 AM. Reason: add workbook

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Calculating Elapsed Time within Variable Working Hours
    By TIAL in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-12-2013, 09:17 PM
  2. [SOLVED] Calculating elapsed time,respecting working hours.
    By CoryX in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-10-2013, 04:13 PM
  3. Replies: 31
    Last Post: 09-06-2012, 09:42 AM
  4. [SOLVED] Calc a running total of time elapsed from a timestamp
    By AWilson012 in forum Excel General
    Replies: 3
    Last Post: 07-11-2012, 03:19 PM
  5. Generate total elapsed time with DATEDIF
    By jeffreybrown in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-01-2012, 06:45 AM

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