Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts

    If Date is Day Before or After Holiday

    Hello
    Is there a way for Excel to declare if a date is the day before or after a specified date? I have a list of Holidays below that I would like to compare dates to and if the date is one day before any holiday state BEFORE and if the day is one day after a holiday state AFTER. I'm sure you could do it with nested IF statements but is there any easier way?

    Holidays
    1/1/2013
    1/21/2013
    2/12/2013
    2/18/2013
    5/8/2013
    5/27/2013
    7/4/2013
    9/2/2013
    10/14/2013
    11/11/2013
    11/28/2013
    11/29/2013
    12/25/2013

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Mostate,

    Here's one possible solution:
    =IF(NOT( ISNA(VLOOKUP(E2,$A$2:$A$14,1,FALSE))),"Before",IF( NOT(ISNA(VLOOKUP(E2,$B$2:$B$14,1,FALSE))),"After", ""))
    Holidays.JPG
    Here's the test file.
    HolidayBeforeAfter.xlsx
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    FYI the Match function can also be used to accomplish this task.
    =IF(NOT(ISNA(MATCH(E2,$A$2:$A$14,0))),"Before",IF( NOT(ISNA(MATCH(E2,$B$2:$B$14,0))),"After",IF(NOT(I SNA(MATCH(E2,$C$2:$C$14,0))),"Holiday","")))
    Holidays2.JPG
    Test File:
    HolidayBeforeAfter.xlsx
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. The Following User Says Thank You to RetiredGeek For This Useful Post:

    MOSTATE (2013-11-08)

  5. #4
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    This is perfect, just what I was looking for...I will try the Match function as well...Thanks so much!!

  6. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You can do it directly from the Holiday list without the extra columns if desired:
    =IF(ISNUMBER(MATCH(E2+1,$C$2:$C$14,0)),"Before",IF (ISNUMBER(MATCH(E2-1,$C$2:$C$14,0)),"After",""))

    Steve

  7. The Following User Says Thank You to sdckapr For This Useful Post:

    RetiredGeek (2013-11-08)

  8. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Mostate,

    Gald to be of assistance.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Mind boggling!

    How about a user defined function. Place the code in a standard module then apply the function like you would any other Excel function.
    Place the formula:

    =CheckDate(A2)

    in cell B2 then copy down.

    HTH,
    Maud

    Holidays1.png

    Code:
    Public Function CheckDate(celldate As Date) As String
    Application.Volatile
    'DECLARE AND SET ARRAY VARIABLE
    Dim Holiday(13) As Date
    Holiday(1) = #1/1/2013#
    Holiday(2) = #1/21/2013#
    Holiday(3) = #2/12/2013#
    Holiday(4) = #2/18/2013#
    Holiday(5) = #5/8/2013#
    Holiday(6) = #5/27/2013#
    Holiday(7) = #7/4/2013#
    Holiday(8) = #9/2/2013#
    Holiday(9) = #10/14/2013#
    Holiday(10) = #11/11/2013#
    Holiday(11) = #11/28/2013#
    Holiday(12) = #11/29/2013#
    Holiday(13) = #12/25/2013#
    '-------------------------------------------------
    'DETERMINE RELATIONSHIP OF DATE TO HOLIDAYS
    For I = 1 To 13
        If celldate = Holiday(I) + 1 Then
            CheckDate = "After"
            Exit Function
        ElseIf celldate = Holiday(I) - 1 Then
            CheckDate = "Before"
            Exit Function
        End If
    Next I
    CheckDate = "" 'NEITHER
    End Function
    Attached Files Attached Files

  10. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Hey Y'all,

    Just to Summarize and also show how there is always more than one way to skin an Excel "Cat" here's a workbook that shows 4 different ways to do what the OP requested. There are really more but I incorporated Steve's little trick (I love that one Steve) into my previous examples and I also modified Maudibe's code to use the table to make it easier to add/subtract holidays from the list. I also made all the examples (with the exception of Maudibe's UDF) use a Dynamic Range Name again to make it easier to add/subtract holidays and make the formulas/code easier to read. I also used a different VBA code construct in my copy of the UDF just to show contrasting coding techniques. I hope someone out there finds this useful.

    HolidayBeforeAfter.xlsm
    Last edited by RetiredGeek; 2013-11-08 at 22:35.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    To display "Holiday" as one of the results, you could also change the last line of my code

    from:
    CheckDate = ""

    To:
    CheckDate = "Holiday"

    Maud

  12. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Maud,

    Sorry but that won't work as it will return Holiday for all days that do not match also. You need to add another test.
    badhol.JPG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Yep, Your right RG! (Slap the forehead) How about:
    Code:
    Public Function CheckDate(celldate As Date) As String
    Application.Volatile
    'DECLARE AND SET ARRAY VARIABLE
    Dim Holiday(13) As Date
    Holiday(1) = #1/1/2013#
    Holiday(2) = #1/21/2013#
    Holiday(3) = #2/12/2013#
    Holiday(4) = #2/18/2013#
    Holiday(5) = #5/8/2013#
    Holiday(6) = #5/27/2013#
    Holiday(7) = #7/4/2013#
    Holiday(8) = #9/2/2013#
    Holiday(9) = #10/14/2013#
    Holiday(10) = #11/11/2013#
    Holiday(11) = #11/28/2013#
    Holiday(12) = #11/29/2013#
    Holiday(13) = #12/25/2013#
    '-------------------------------------------------
    'DETERMINE RELATIONSHIP OF DATE TO HOLIDAYS
    For I = 1 To 13
        If celldate = Holiday(I) + 1 Then
            CheckDate = "After"
            Exit Function
        ElseIf celldate = Holiday(I) - 1 Then
            CheckDate = "Before"
            Exit Function
        ElseIf celldate = Holiday(I) Then
            CheckDate = "Holiday"
            Exit Function
        End If
    Next I
    
    End Function

  14. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Maud,

    Right On!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    RetiredGeek
    I would recommened checking for the Holiday first, then before and after.
    =IF(ISNUMBER(MATCH(E2,$C$2:$C$14,0)),"HOLIDAY",IF( ISNUMBER(MATCH(E2+1,$C$2:$C$14,0)),"Before",IF(ISN UMBER(MATCH(E2-1,$C$2:$C$14,0)),"After","")))

    We often have 2 holidays in a row, (day after and Thanksgiving, Christmas and day before). If that is the case, and you enter the Holiday of Christmas eve or the holiday of the day after Thanksgiving. These are Holidays and also 1 day before/after a holiday. If you check for the holiday first they will be displayed as a holiday instead of before/after as your formula will give.

    Steve
    PS. Maudibe
    I recommend changing the IF logic as well for that same reason:
    Code:
    For I = 1 To 13
        If celldate = Holiday(I)  Then
            CheckDate = "Holiday"
            Exit Function
        ElseIf celldate = Holiday(I) - 1 Then
            CheckDate = "Before"
            Exit Function
        ElseIf celldate = Holiday(I) + 1 Then
            CheckDate = "After"
            Exit Function
        End If
    Next I
    Last edited by sdckapr; 2013-11-09 at 08:07.

  16. #14
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Steve,

    Good Point! Thanks! :Cheers:
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  17. #15
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Example updated per Maud's and Steve's suggestions.

    HolidayBeforeAfter.xlsm
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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