Excel Formula for Holiday Dates: Fixed and Moving Days

For a holiday that falls on the same month and day each year, use =DATE($B$1,1,1), assuming the year is in B1; for 2026, it returns January 1, 2026. For holidays that move by weekday, such as U.S. Thanksgiving, use a formula based on the year and weekday rule—examples below show how to build both types of dates in Excel for Microsoft 365.

What an Excel holiday-date formula does

A holiday date can be a fixed calendar date, such as January 1, or a date determined by a rule, such as the fourth Thursday in November. Excel stores dates as serial numbers, so formulas can calculate them, compare them, sort them, and use them in workday calculations. The displayed appearance depends on the cell’s number format, not on the underlying value.

It helps to separate three ideas that are often mixed together:

Calendar date: the date on which the holiday officially falls.
Observed date: a substituted day off when a holiday falls on a weekend, if the relevant calendar uses that convention.
Work-calendar date: a date you choose to exclude from a business-day calculation. This can include holidays, company closures, or other nonworking days.

A formula can calculate a date from a rule, but it cannot determine which holidays apply to every country, employer, or person. Confirm the applicable calendar before using a generated list for scheduling or reporting.

Calculate a fixed-date holiday

For a holiday that always falls on the same month and day, use Excel’s DATE function. Put the year in one cell and use the month and day as numbers in the formula. For example, with the year 2026 in B1, the New Year’s Day formula shown in the opening paragraph returns January 1, 2026. For Christmas, change the month and day arguments to 12 and 25.

For a reusable holiday table, place years down column A and put a formula beside each year. With the year in A2, use =DATE(A2,12,25) in B2 to calculate Christmas Day. For 2026, B2 returns December 25, 2026; fill the formula down to calculate the date for each year in column A. Because the reference A2 is relative, it changes to A3, A4, and so on as you fill down.

The dollar signs in =DATE($B$1,1,1) lock the year reference when you copy the formula elsewhere. This is useful when several holiday formulas refer to one selected year in B1. If you want to copy a formula down a list of different years, use a relative reference such as A2 instead.

Calculate holidays defined by a weekday

For a holiday defined as the first, second, third, or fourth occurrence of a weekday in a month, calculate the first day of that month and move forward to the required weekday. In the following formula, B1 contains the year. It calculates U.S. Thanksgiving as the fourth Thursday in November: =DATE($B$1,11,1)+MOD(4-WEEKDAY(DATE($B$1,11,1),2),7)+21. For 2026, the result is November 26, 2026.

The WEEKDAY function’s second argument is 2, which numbers Monday as 1, Tuesday as 2, and Thursday as 4. The MOD portion finds how many days to move from the first of the month to the first target weekday. Adding 21 days advances three more weeks, making the result the fourth Thursday. This construction is useful for rules such as the third Monday of a month as well: change the weekday number and the number of extra weeks.

For example, Labor Day in the United States is the first Monday in September. With the year in B1, use =DATE($B$1,9,1)+MOD(1-WEEKDAY(DATE($B$1,9,1),2),7). For 2026, September 1 is a Tuesday, so the formula returns September 7, 2026. Here, the weekday target is 1 for Monday, and no additional weeks are added because the rule calls for the first Monday.

Find the last weekday of a month

Some holidays are defined by the last occurrence of a weekday in a month. U.S. Memorial Day, for instance, falls on the last Monday in May. With the year in B1, use =EOMONTH(DATE($B$1,5,1),0)-MOD(WEEKDAY(EOMONTH(DATE($B$1,5,1),0),2)-1,7). For 2026, it returns May 25, 2026.

EOMONTH returns the final calendar date in the specified month. WEEKDAY identifies that date’s weekday, and the MOD calculation subtracts the number of days needed to reach the previous Monday, or the same day if month-end is already Monday. To adapt this pattern for another weekday, change the target number in the subtraction: Monday is 1, Tuesday is 2, through Sunday as 7 when WEEKDAY uses return type 2.

When building a general holiday calendar, check that the written rule really is the same every year. Some observances move between months, depend on lunar or religious calendars, or use a jurisdiction-specific rule that is not captured by a simple weekday formula.

Generate an observed date when a holiday falls on a weekend

Some organizations observe a Saturday holiday on the preceding Friday and a Sunday holiday on the following Monday. If that is the rule you need, and the actual holiday date is in A2, use =A2+IF(WEEKDAY(A2,2)=6,-1,IF(WEEKDAY(A2,2)=7,1,0)). For July 4, 2026, which falls on a Saturday, the formula returns July 3, 2026.

This is only a particular weekend-substitution rule; it is not a universal definition of an observed holiday. Rules differ by country, institution, and employer, and consecutive holidays can make substitution more complicated. Keep the actual date and observed date in separate columns when both matter. That preserves the original calendar date while letting a schedule use the applicable day off.

Use holiday dates in workday calculations

Once the holiday dates are in a range, Excel can exclude them from business-day calculations. Suppose A2 contains January 1, 2026, B2 contains January 9, 2026, and F2:F12 is the holiday list with January 1 in F2 and the remaining cells blank. The formula =NETWORKDAYS(A2,B2,$F$2:$F$12) returns 6. There are seven Monday-to-Friday dates in that inclusive range, and excluding New Year’s Day leaves six working days.

To calculate a date a number of working days after a starting date, use WORKDAY. With January 2, 2026, in A2 and the same holiday range in F2:F12, =WORKDAY(A2,5,$F$2:$F$12) returns January 9, 2026. WORKDAY does not count the start date as the first workday; it moves forward through weekdays and skips dates in the holiday range. These functions treat Saturday and Sunday as weekends by default. If your workweek differs, Excel also provides NETWORKDAYS.INTL and WORKDAY.INTL for custom weekend patterns.

Keep the holiday list as actual Excel dates, not text that merely looks like a date. A separate, clearly labeled range makes the calendar easier to audit and update. If a holiday is added or a date changes, the workday formulas will use the updated range automatically as long as the new date is inside the referenced cells.

Format and check the results

If a formula displays a number such as 46023 instead of a familiar date, the calculation may be correct: the cell is probably formatted as General or Number. Select the result cells and choose Home > Number Format, then select Short Date or Long Date. On Windows, Ctrl+1 opens Format Cells; on Mac, use Command+1. You can also use the number-format controls on the Home tab in Excel for the web.

Check a new holiday formula against a trusted calendar for several years, especially when the result will feed a deadline or staffing plan. Common causes of unexpected results include entering a year as text, using the wrong weekday numbering system, referring to the wrong month, or confusing an actual holiday with its observed date. A date that appears one day off can also reflect a different local holiday rule rather than an Excel error.

Excel formulas use comma separators in the examples here. In some regional settings, Excel expects semicolons as argument separators; if Excel rejects a correctly structured formula, check the list separator configured for your installation. The function names and calculations remain the same.

Choose a method for your calendar

For a short list with a few fixed dates, separate DATE formulas are easy to read and maintain. For weekday-based holidays, formulas based on WEEKDAY and MOD are more reliable than typing a new date every year, provided the holiday rule is accurately represented. If several people rely on the calendar, keep the holiday names, actual dates, and any observed dates in a labeled table so another person can verify the assumptions.

Use DATE for holidays tied to the same month and day each year.
Use a weekday formula for rules such as the first Monday or fourth Thursday in a month.
Use an explicit list when dates follow a complex or changing calendar rule that should not be inferred from a simple formula.
Use NETWORKDAYS or WORKDAY when the goal is to count working days or calculate a business-day deadline while excluding holidays.

Formula-driven dates are convenient, but they do not replace a validated holiday schedule. A formula can be mathematically correct and still encode the wrong jurisdiction, observance policy, or year-specific exception. Document the rule beside the formula or include a source and review date in the calendar table.

Frequently Asked Questions

What is the simplest Excel formula for a holiday date?
For a fixed date, use DATE with the year, month, and day. If B1 contains 2026, =DATE($B$1,1,1) returns January 1, 2026.

How do I calculate Thanksgiving in Excel?
For U.S. Thanksgiving, use the fourth-Thursday formula in the weekday section with the year in B1. It returns November 26 for 2026. Other countries may use a different holiday date or rule.

Can Excel automatically list every public holiday?
There is no single formula that automatically knows every country’s official holidays and local observance rules. Create or import a verified holiday list, or write formulas for the specific rules that apply to your calendar.

Why does my holiday formula show a number?
Excel stores dates as serial numbers. Apply Short Date or Long Date formatting to the result cell to display the serial value as a calendar date.

How do I exclude holidays from a deadline calculation?
Put the holiday dates in a range and supply that range as the holiday argument to WORKDAY or NETWORKDAYS. Make sure the range contains real date values and includes all relevant holidays.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.