Function Problems

How to Calculate Closure Dates and Holidays with Excel Formulas

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs to classify dates as YES, NO, or HOLIDAY based on location-specific closure rules, filter specific items like pools from a shared N/A column, and automatically count the occurrences of each classification.

Product: Excel
Device & OS: not provided
Scenario: Tracking location-specific closures, managing holiday dates, and generating automatic status counts from a dataset.
Observed behavior: Dates need to be accurately categorized as YES, NO, or HOLIDAY using formulas, and the total counts for each status should be calculated automatically.

Before you start

Ensure you have a complete list of official holiday dates for your location and clear criteria for your closure rules before building the logical formulas.

Solution 1Recommended

Classify Dates using Nested IF and Logical Functions

Use a combination of IF, AND, OR, and MONTH functions to accurately check if a date falls on a holiday or meets specific closure criteria.

Set up a holiday reference range

Create a separate column or table containing your official holiday dates to use as a reference range in your formulas.

Prioritize the holiday check

Write a nested IF formula starting with the holiday check to ensure holidays are flagged first. For example, use =IF(COUNTIF(HolidaysRange, A2)>0, "HOLIDAY", ...) to evaluate the date.

Add location-specific closure rules

Add AND/OR conditions for your specific closure rules inside the nested IF statement, such as checking the month or weekday: IF(AND(MONTH(A2)=11, WEEKDAY(A2)=2), "NO", "YES").

Filter shared columns for specific items

To evaluate a specific item like a 'pool' in a shared N/A column (ignoring other items like hot tubs), nest an IF statement that checks the item name column before applying the closure logic.

Solution 2

Automatically Count Classifications with COUNTIF

Summarize the total number of YES, NO, and HOLIDAY statuses using the COUNTIF function.

Manage Complex Formulas with WPS Office

Easily Calculate Dates and Counts in WPS Spreadsheet

WPS Spreadsheet fully supports advanced logical functions like nested IF, AND, OR, and COUNTIF, allowing you to seamlessly manage location closures and holiday schedules.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your dates and closure schedules.
2. Insert logical functions: Click on the 'Formulas' tab and select 'Insert Function' to easily build your logical tests with a user-friendly interface.
3. Input formulas: Use the formula bar to input your nested IF and COUNTIF functions exactly as you would in standard spreadsheet software.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Lightweight software with fast processing for large datasets and complex logic.Built-in function hints and syntax helpers to assist with nested formulas.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my formula not prioritizing the holiday status?

Spreadsheet formulas evaluate nested IF statements in exact order. You must place the holiday check at the very beginning of the formula so it correctly returns 'HOLIDAY' before checking for any subsequent closure rules.

How can I make the COUNTIF function count based on multiple criteria?

If you need to count based on multiple conditions, such as counting 'YES' statuses only for a specific location, use the COUNTIFS function instead of COUNTIF. The syntax allows you to add multiple range and criteria pairs.

How do I evaluate a shared N/A column for only one specific item type?

You can use an IF function to first check the item description column (e.g., IF(ItemCell="Pool", ...)) before evaluating the closure status. This ensures the formula ignores other items like hot tubs within the shared column.

Excel & Data Problems