+ Reply to Thread
Results 1 to 6 of 6

Calculating Elapsed time minus holidays and weekends

  1. #1
    Registered User
    Join Date
    12-04-2013
    Location
    nashville, tn
    MS-Off Ver
    Excel 2010
    Posts
    23

    Calculating Elapsed time minus holidays and weekends

    The attached spreadsheet has a formula in column L that I need help with.

    I am looking to find the elapsed time in hh:mm between the "start date" and the "resolved date". I need to make sure to eliminate any standard holidays and weekends and only calculate for a standard 8a-5p day. Please take a look at what I have so far. Any help would be greatly appreciated!

    Using:
    =IF(OR($L101="",$M101=""),"",(NETWORKDAYS($W101,$X101)-2)*($AA$2107-$AA$2106)+MAX(0,$AA$2107-MAX(TIME(HOUR($W101),MINUTE($W101),SECOND($W101)),$AA$2106))+MAX(0,MIN(TIME(HOUR($X101),MINUTE($X101),SECOND($X101)),$AA$2107)-$AA$2106))
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,064

    Re: Calculating Elapsed time minus holidays and weekends

    I see that some of these have start dates that are outside normal working hours. Is that for real? If so it considerably adds to the difficulty.

    E.g. Rows:
    1137
    1183
    1271
    etc

    If they're errors, then this is not too horrendous!!
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: Lá fhéile Pádraig sona dhaoibh

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,064

    Re: Calculating Elapsed time minus holidays and weekends

    Also some of them have end dates and no start dates!!??

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,064

    Re: Calculating Elapsed time minus holidays and weekends

    FWIW, this is grand for the vast majority, pending answers to my comments:

    =NETWORKDAYS(J2,I2,$N$2:$N$5)*TIME(8,0,0)-(TIME(HOUR(J2),MINUTE(J2),0)-TIME(9,0,0))-(TIME(17,0,0)-TIME(HOUR(I2),MINUTE(I2),0))

    where holidays are in N2 to N5. i don't know what these are. Enter yourself and adjust the ranges appropriately.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-04-2013
    Location
    nashville, tn
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Calculating Elapsed time minus holidays and weekends

    Thank you, Glenn!!!

    Yes, it is normal for the start date to start outside of normal business hours. And I should have deleted those rows with blanks for hte start dates.

    So for rows N2 to N5 is there a specific format I need to put in those cells for the holidays? For4 example, should it look like "2/2/2015 9:14:39 AM" with time and all, or can I just put a date?

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,064

    Re: Calculating Elapsed time minus holidays and weekends

    Regarding holidays.... Just enter the dates.

    regarding start dates outside normal business hours, so.... what do you want Excel to do????

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 0
    Last Post: 10-31-2014, 10:32 AM
  2. [SOLVED] Calculating business hour between two days excluding holidays and weekends
    By christophertpj in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-13-2014, 09:00 AM
  3. [SOLVED] 3 days prior to today minus weekends and holidays
    By Thunderer in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-17-2014, 09:54 PM
  4. [SOLVED] Elapsed Days Hours Minutes Excluding Weekends and Holidays
    By moshjosh in forum Excel General
    Replies: 7
    Last Post: 12-10-2012, 08:39 AM
  5. Replies: 10
    Last Post: 11-29-2011, 08:21 PM
  6. Calculating Date with # of days minus weekends
    By Lazhal in forum Excel General
    Replies: 2
    Last Post: 06-10-2011, 03:28 PM
  7. how to calculate elapsed time (minus weekends/holidays)?
    By blizard in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-02-2005, 10:19 AM

Tags for this Thread

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