Elapsed Time (h):mm:ss
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: Elapsed Time (h):mm:ss
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    Board Regular
    Join Date
    Jul 2014
    Location
    Memphis, TN
    Posts
    2,636
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Elapsed Time (h):mm:ss

    The formula I provided was copied from a tested environment.
    It calculate the total elapsed time in the first part. Then subtracts the number of days not available. The number of days not available is the total number of days less the number of workdays as provided by the Excel function NETWORKDAYS.
    Excel 2013, 2016 with PowerBI
    Knowing that it can be done is half the battle!

  2. #12
    New Member
    Join Date
    Jul 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Elapsed Time (h):mm:ss

    I re-formatted the columns and this does seem to work other than it is adding 24 hours to the results?

  3. #13
    New Member
    Join Date
    Jul 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Elapsed Time (h):mm:ss

    I used a deferent formula that seems to be working i.e. +NETWORKDAYS(stat,end,Holdays)+start-end-INT(end-start)-1 The only issue with this is the elapsed time columns full of #### what would need to be added for the elapsed time column to be blank until there is a date/time in the end column?

  4. #14
    New Member
    Join Date
    Jul 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Elapsed Time (h):mm:ss

    Sorry beginning of formula is =NETWORKDAYS not +

  5. #15
    Board Regular
    Join Date
    Jul 2014
    Location
    Memphis, TN
    Posts
    2,636
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Elapsed Time (h):mm:ss

    Quote Originally Posted by Red Corvette View Post
    Sorry beginning of formula is =NETWORKDAYS not +
    That's a LOTUS thing.

    "########" as a result formatted as a date/time may indicate the resultant value is Negative. Try inversing with a -1.
    Excel 2013, 2016 with PowerBI
    Knowing that it can be done is half the battle!

  6. #16
    Board Regular
    Join Date
    Jul 2014
    Location
    Memphis, TN
    Posts
    2,636
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Elapsed Time (h):mm:ss

    Quote Originally Posted by SpillerBD View Post
    Code:
    =(EndDate-StartDate)-(DAYS(EndDate,StartDate)-NETWORKDAYS(StartDate,EndDate,HolidayList))
    NETWORKDAYS will provide a result of 1 for the same day.
    Minor modofication.

    Code:
    =(EndDate-StartDate)-(DAYS(EndDate,StartDate)-NETWORKDAYS(StartDate,EndDate,HolidayList)-1)
    Excel 2013, 2016 with PowerBI
    Knowing that it can be done is half the battle!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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