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")
We can then click and drag this formula down to each remaining cell in column B:
The formula now returns either “Yes” or “No” to indicate whether or not the corresponding date in column A is a holiday.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: Calculate the Number of Months Between Dates
Excel: How to Calculate Sum by Date
Excel: How to Calculate Average by Date
