+ Reply to Thread
Results 1 to 12 of 12

Subtracting 2 dates, exclude Sundays & Holidays (in list) - outside US

  1. #1
    Registered User
    Join Date
    02-18-2014
    Location
    Ampang, Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    35

    Subtracting 2 dates, exclude Sundays & Holidays (in list) - outside US

    I got another issue too here...

    Subtract these 2 dates, exclude Sundays & Holidays (in a list)

    And I'm outside from US.

    Eg:
    DATE ISSUE DATE CLOSED
    A B
    21/1/2013 21/2/2013
    14/2/2013 28/5/2013

    Is someone could help me??

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Subtracting 2 dates, exclude Sundays & Holidays (in list) - outside US

    WORKDAY.INTL can help.
    Quang PT

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Subtracting 2 dates, exclude Sundays & Holidays (in list) - outside US

    Shouldn't it be the NETWORKDAYS.INTL function ( the WORKDAY function adds days to a date)

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Subtracting 2 dates, exclude Sundays & Holidays (in list) - outside US

    Pl see attached file.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    02-18-2014
    Location
    Ampang, Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    35

    Re: Subtracting 2 dates, exclude Sundays & Holidays (in list) - outside US

    I've tried NETWORKDAYS.INTL function...but it still counting in US holidays.

    Oh well, kvsrinivasamurthy...it works, however it still counts Sundays.

    I just need Mondays to Saturdays (which exclude Sundays and holidays)

    Could you please help me with this??
    Last edited by husni; 02-19-2014 at 05:51 AM.

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Subtracting 2 dates, exclude Sundays & Holidays (in list) - outside US

    See my post no 4

  7. #7
    Registered User
    Join Date
    02-18-2014
    Location
    Ampang, Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    35

    Re: Subtracting 2 dates, exclude Sundays & Holidays (in list) - outside US

    I send the copy of mine.

    I got another value for your calculation.

    Could you please again help me with this kvsrinivasamurthy.
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Subtracting 2 dates, exclude Sundays & Holidays (in list) - outside US

    Formulas used are ARRAY Formulas.To enter ARRAY formula You Should press Ctrl+Shift+enter not only Enter keys.
    Select C2 Press F2
    Now Press Ctrl+Shift+enter
    You Will se Flower Bracket around Formla.
    Drag Down.
    Now You Will get correct Result.

  9. #9
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Subtracting 2 dates, exclude Sundays & Holidays (in list) - outside US

    Quote Originally Posted by husni View Post
    I've tried NETWORKDAYS.INTL function...but it still counting in US holidays.
    As you should enter the holidays yourself, I don't see why you are talking about US holidays? Check the function syntax to see how you can exclude Sundays

  10. #10
    Registered User
    Join Date
    02-18-2014
    Location
    Ampang, Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    35

    Re: Subtracting 2 dates, exclude Sundays & Holidays (in list) - outside US

    Last question for this thread.

    what if:
    DATE ISSUE DATE CLOSED
    A B C
    21/1/2013 21/2/2013 9 days
    14/2/2013

    The B2 is still no value (blank). Is there any additional to the formula???

    Because i got #REF when no date at B2.

    Can help me on this???

  11. #11
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Subtracting 2 dates, exclude Sundays & Holidays (in list) - outside US

    To avoid error result, use =IFERROR("Your Formula");"")

  12. #12
    Registered User
    Join Date
    02-18-2014
    Location
    Ampang, Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    35

    Re: Subtracting 2 dates, exclude Sundays & Holidays (in list) - outside US

    Accomplished.

    TQVM azumi, Pepe & kvsrinivasamurthy.

+ 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. hour difference between two dates/times, exclude weekends and holidays
    By lamdl in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-23-2014, 09:11 PM
  2. Replies: 6
    Last Post: 09-09-2013, 08:54 AM
  3. Replies: 10
    Last Post: 11-29-2011, 08:21 PM
  4. Exclude Sundays between two dates
    By buffalobill in forum Excel General
    Replies: 4
    Last Post: 03-27-2010, 05:41 AM
  5. String of dates to exclude weekends AND holidays
    By FEI7774 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-01-2009, 06:19 PM

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