Table of Contents
In Excel, you can create a formula to check if a given date is a holiday. You can create a list of holidays in a separate sheet and use the IF, MATCH, and ISNUMBER functions together in an IF statement to check if a given date is in the list of holidays. This formula will return TRUE if the date is a holiday, and FALSE if it is not.
You can use the following formula in Excel to check if a given date is a holiday:
=IF(OR(COUNTIF(A2,$D$2:$D$9)), "Holiday", "Not a Holiday")
This particular formula checks if the date in cell A2 is a holiday or not.
Note: This formula assumes that the dates that are considered holidays are listed in the range D2:D9.
The following example shows how to use this formula in practice.
Example: How to Check if Date is a Holiday in Excel
Suppose we have a list of dates in column A and a list of holidays in column D:
Suppose we would like to check if each date in column A is a holiday or not.
We can type the following formula into cell B2 to do so:
=IF(OR(COUNTIF(A2,$D$2:$D$9)), "Holiday", "Not a Holiday")
We can then click and drag this formula down to each remaining cell in column B:
Column B now tells us if each corresponding date in column A is a holiday or not.
Note that we chose to return either “Holiday” or “Not a Holiday” as results from the IF function, but you can choose to return whatever values you would like.
For example, you could instead type the following formula into cell B2:
=IF(OR(COUNTIF(A2,$D$2:$D$9)), "Yes", "No")
The formula now returns either “Yes” or “No” to indicate whether or not the corresponding date in column A is a holiday.