+ Reply to Thread
Results 1 to 3 of 3

Tracking elapsed business hours

  1. #1
    Registered User
    Join Date
    03-25-2005
    Posts
    9

    Tracking elapsed business hours

    I'm looking for a function that will track elapsed business hours only. So, it should exclude non-business hours, holidays, and weekends. I've tried the function below, but it fails if the start date/time or end date/time fall outside of business hours. Even though I don't want to track non-business hours, I do have to be able to log them.

    =ROUND(IF(NETWORKDAYS(A1,B1,luHolidays)=1,(MIN(luDayEnd,MOD(B1,1))-MAX(luDayStart,MOD(A1,1))),(NETWORKDAYS(A1,B1,luHolidays)-2)*(600/1440)+(luDayEnd-MAX(MOD(A1,1),luDayStart))+MIN(MOD(B1,1),luDayEnd)-luDayStart)*24,0)/24

    A1 contains the logged start date and time (e.g. 2006.01.22 7:00 AM)
    B1 contains the logged end date and time (e.g. 2006.01.23 10:00 AM)
    luHolidays contains the list of holidays
    luDayStart contains the beginning of the business day (e.g. 8:00 AM)
    luDayEnd contains the end of the business day (e.g. 5:00 PM)

    Can any of you experts help me?

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,704
    Try this,

    =(NETWORKDAYS(A1,B1, luHolidays)-1)*(luDayEnd-luDayStart)+IF(NETWORKDAYS(B1,B1,luHolidays),MEDIAN(MOD(B1,1),luDayEnd, luDayStart), luDayEnd)-MEDIAN(NETWORKDAYS(A1,A1, luHolidays)*MOD(A1,1), luDayEnd, luDayStart)

    format result cell as [h]:mm

  3. #3
    Registered User
    Join Date
    03-25-2005
    Posts
    9

    Awesome!

    The formula works perfectly. Thank you.

+ 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