The Problem
Dates often look harmless until they affect pay rules, deadlines, or staffing plans. A holiday hidden in the middle of a service log can change the right answer completely, especially when the same sheet is used for payroll and scheduling.
Checking those dates by eye is slow and easy to mess up. You end up flipping between the schedule and the holiday calendar, and one missed row can throw off the summary. This challenge is about turning that manual lookup into a repeatable formula.
How We Solve It
We keep an official holiday list in one place, then test each service date against it. If Excel finds the date in that registry, the row is a holiday. If not, it is a normal work day.
Method 1: MATCH plus ISNUMBER
Method 1: Search for the date in a fixed holiday list with MATCH.
This is the method used in the solution. MATCH looks for the service date inside the holiday range. When it finds a match, it returns a position number. ISNUMBER turns that into a simple TRUE or FALSE test, and IF converts the result into a readable label.
This solves the problem by checking each service date against the official registry instead of relying on memory or visual scanning. If the date exists in the holiday list, the row is marked HOLIDAY. If it does not, the row stays a normal WORK DAY.
=IF(ISNUMBER(MATCH(A2, $F$2:$F$4, 0)), "HOLIDAY", "WORK DAY")
Method 2: COUNTIF as a quick existence check
Method 2: Use COUNTIF when you only need to know whether a date exists in the list.
If you do not care about position and only need to know whether the date appears in the registry, COUNTIF is a clean option. A result above zero means the date is on the holiday list. This version is easy to read and works well for a simple yes or no status column.
This solves the same holiday-checking problem in a more direct way. Instead of asking where the date appears, it only asks whether it appears at all, which is enough when the final goal is just to label the day correctly.
=IF(COUNTIF($F$2:$F$4, A2) > 0, "HOLIDAY", "WORK DAY")
Method 3: Return the holiday name
Method 3: Pull the holiday name itself when the report needs more detail.
Sometimes a label like HOLIDAY is enough. Other times you want to see which holiday it is. In that case, a lookup formula such as VLOOKUP can return the event name from the registry, which is helpful for reports that need more context.
This solves a slightly richer version of the problem. Instead of only proving that a row lands on a holiday, it brings back the holiday name from the registry, which makes the schedule easier to explain in payroll or planning reports.
=VLOOKUP(A2, $F$2:$G$4, 2, FALSE)
Function Explanation
1. MATCH
MATCH searches a range and returns the position of the value it finds. With the third argument set to 0, it looks for an exact match, which is what we want for holiday dates.
Learn more this functionMATCH
2. COUNTIF
COUNTIF counts how many cells match a condition. In this file, it can either test whether a date exists in the holiday list or count how many final results say HOLIDAY.
3. ISNUMBER
ISNUMBER is the small helper that makes the MATCH pattern easier to use. Found dates return a number, and missing dates return an error. This function lets us turn that into clean logic for IF.
The important part is keeping the holiday registry accurate. If the list on the right is incomplete, the formula will still work perfectly, but it will be answering the wrong question.
Check each service date against the holiday registry in F2:F4, mark the row as HOLIDAY or WORK DAY, and then finish the summary so the sheet shows how many records were reviewed and how many holidays were found.