# How to check if a date is a holiday in Excel

Count the date in the holiday list: `=IF(COUNTIF(holidays,A2),"Holiday",IF(WEEKDAY(A2,2)>5,"Weekend","Workday"))`.

**Excel:** `=IF(COUNTIF($E$2:$E$7,A2),"Holiday",IF(WEEKDAY(A2,2)>5,"Weekend","Workday"))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Date | Day type |  | Holiday | Date |
| 2026-05-25 |  |  | New Year's Day | 2026-01-01 |
| 2026-05-26 |  |  | Memorial Day | 2026-05-25 |
| 2026-07-03 |  |  | Independence Day (observed) | 2026-07-03 |
| 2026-07-04 |  |  | Labor Day | 2026-09-07 |
| 2026-11-26 |  |  | Thanksgiving | 2026-11-26 |
| 2026-11-27 |  |  | Christmas | 2026-12-25 |
| 2026-12-27 |  |  |  |  |

Formula in B2, filled to B8: `=IF(COUNTIF($E$2:$E$7,A2),"Holiday",IF(WEEKDAY(A2,2)>5,"Weekend","Workday"))`

Result: Holiday; Workday; Holiday; Weekend; Holiday; Workday; Weekend

## How it works

COUNTIF counts how many times the date appears in the holiday list, and any count above 0 counts as TRUE. Dates that aren't holidays go on to the weekend test, where WEEKDAY with type 2 gives 6 and 7 for Saturday and Sunday.

## Other ways

- `=IF(ISNUMBER(MATCH(A2,$E$2:$E$7,0)),"Holiday",IF(WEEKDAY(A2,2)>5,"Weekend","Workday"))`: MATCH instead of COUNTIF; the same result.
- `=IF(NETWORKDAYS(A2,A2,$E$2:$E$7),"Workday","Day off")`: One test for weekends and holidays together. (Doesn't say which kind of day off.)

## Common mistakes

- A holiday list typed as text never matches real dates. Real dates are right-aligned.
- Dates with times don't match the plain holiday dates. Compare `INT(A2)`.

## Questions

**How do I return the holiday's name?**

`=XLOOKUP(A2,$E$2:$E$7,$D$2:$D$7,"")`.

**How do I skip holidays when adding days?**

Pass the list to WORKDAY: `=WORKDAY(A2,5,$E$2:$E$7)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/check-if-a-date-is-a-holiday