+ Reply to Thread
Results 1 to 8 of 8

Date should exclude holiday which saved separately at sheet 2.

  1. 08-19-2013, 07:16 AM #1
    ExcelUser2707
    ExcelUser2707 is offline
    Forum Contributor
    Join Date
    07-16-2013
    Location
    India
    MS-Off Ver
    Excel 2013
    Posts
    124

    Date should exclude holiday which saved separately at sheet 2.

    Hi all

    Please help

    I am maintaining tracking on date base, but holiday is also calculating in same. Require to exclude holiday which is saved separately in sheet 2.
    Require excluding value (holiday) and calculate the dates.

    Holiday example is attached here for reference

    Thanks In Advance
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 08-19-2013, 07:21 AM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Date should exclude holiday which saved separately at sheet 2.

    C1: -15
    D1: -5

    C3: =WORKDAY($B3, C$1, 'Holiday List'!$A:$A)

    Now copy C3 down and across the table.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 08-19-2013, 07:37 AM #3
    ExcelUser2707
    ExcelUser2707 is offline
    Forum Contributor
    Join Date
    07-16-2013
    Location
    India
    MS-Off Ver
    Excel 2013
    Posts
    124

    Re: Date should exclude holiday which saved separately at sheet 2.

    Thanks a lot for your prompt reply.

    I want to say excel will auto get new date, which is not holiday.

    e.g. 1st reminder date contend – 15/08/2013 and if the in sheet2 have holiday is updated, then it will auto pick next working day i.e. 16/08/2013.
    Reply With QuoteRegister To Reply
  4. 08-19-2013, 01:15 PM #4
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Date should exclude holiday which saved separately at sheet 2.

    You wanted a warning 15 working days prior to an event, the formula shown does that. Now the weekends and holidays are excluded from the count, yuo might need to adjust the -15 to a lower number. Perhaps 10 working days.
    Reply With QuoteRegister To Reply
  5. 08-20-2013, 05:06 AM #5
    ExcelUser2707
    ExcelUser2707 is offline
    Forum Contributor
    Join Date
    07-16-2013
    Location
    India
    MS-Off Ver
    Excel 2013
    Posts
    124

    Re: Date should exclude holiday which saved separately at sheet 2.

    Quote Originally Posted by JBeaucaire View Post
    You wanted a warning 15 working days prior to an event, the formula shown does that. Now the weekends and holidays are excluded from the count, yuo might need to adjust the -15 to a lower number. Perhaps 10 working days.
    Thanks JBeaucaire,

    I am sending manually email based on the reminder date criteria which is saved at column “B” and “C”.

    Column “B” having formula of today (minus) 15 day, & Column “C” having formula of today (minus) 5 day.

    I am facing problem of holiday, on holiday I never open this excel file. Reminder sending wills failure due to holiday.

    That’s why I requires, “B” & “C” column will consider holiday, and pick next day of holiday

    If you will help me, it will work me very useful.

    Thanks for reply and kindly cooperation.
    Reply With QuoteRegister To Reply
  6. 08-20-2013, 09:45 PM #6
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Date should exclude holiday which saved separately at sheet 2.

    How about this:

    C3: =$B3-15+ISNUMBER(MATCH($B3-15, 'Holiday List'!$A:$A, 0))
    D3: =$B3-5+ISNUMBER(MATCH($B3-5, 'Holiday List'!$A:$A, 0))
    Reply With QuoteRegister To Reply
  7. 08-20-2013, 09:55 PM #7
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Date should exclude holiday which saved separately at sheet 2.

    I see, you DO have sequential Holidays, so we need to search forward several days to find a date NOT in the list. So try these array formulas instead:

    C3: =MIN(IF(ISERROR(MATCH(($B3-15)+ROW($1:$5)-1, 'Holiday List'!$A:$A, 0)), ($B3-15)+ROW($1:$5)-1, ""))
    D3: =MIN(IF(ISERROR(MATCH(($B3-5)+ROW($1:$5)-1, 'Holiday List'!$A:$A, 0)), ($B3-5)+ROW($1:$5)-1, ""))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.


    Now Copy C3:D3 downward.
    Reply With QuoteRegister To Reply
  8. 08-21-2013, 01:26 AM #8
    ExcelUser2707
    ExcelUser2707 is offline
    Forum Contributor
    Join Date
    07-16-2013
    Location
    India
    MS-Off Ver
    Excel 2013
    Posts
    124

    Re: Date should exclude holiday which saved separately at sheet 2.

    HI JBeaucaire …Thanks a lot for your kindly assistance and quick reply

    It is awesome, it’s working very well, what efforts you put to create this formula it is very helpful to me for track the records.

    I appreciate your knowledge towards excel, formula & VB.

    Thanks Thanks Thanks,

    God bless you
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Determine last date of attendance as we considered weekend, public holiday and absent date
    By ashburnadam in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-21-2012, 12:08 AM
  2. Next valid date, exclude holiday / Sat / Sun
    By phil77 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-27-2010, 04:56 PM
  3. Saving each sheet separately!
    By kart1224 in forum Excel General
    Replies: 1
    Last Post: 04-28-2007, 04:15 PM
  4. [SOLVED] Inserting the date that an excel sheet is saved into a cell
    By sharock in forum Excel General
    Replies: 3
    Last Post: 01-06-2005, 04:06 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1