Thread: If Date is Day Before or After Holiday

1. 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. 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

3. 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

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

MOSTATE (2013-11-08)

5. This is perfect, just what I was looking for...I will try the Match function as well...Thanks so much!!

6. 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. Mostate,

Gald to be of assistance.

9. 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```

10. 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

11. 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. 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.

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. Maud,

Right On!

15. 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```

16. Steve,

Good Point! Thanks! :Cheers:

17. Example updated per Maud's and Steve's suggestions.

HolidayBeforeAfter.xlsm

Posting Permissions

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