How to Find the Next Holiday in Excel (Formulas + Free Template)

Wondering how many days are left before your next day off? Instead of flipping through a paper calendar, you can make a spreadsheet answer that question for you. With a short list of holiday dates and a few functions, Excel can tell you which holiday comes next, when it falls, and how long you still have to wait.

This guide shows three reliable ways to do it: pick the earliest upcoming date from a list, calculate the date of holidays that always land on the nth weekday, and build a countdown calendar that refreshes itself every day. Every formula below works the same way in WPS Spreadsheet and Microsoft Excel.

How to Find the Next Holiday in Excel (Formulas + Free Template)

What You Need Before You Find the Next Holiday in Excel

All three methods in this article rely on one thing: a column of holiday dates stored as real date values, not text. If your dates are left-aligned in the cell, Excel is probably treating them as text, and comparisons such as >=TODAY() will quietly fail. Type dates in a format your regional settings recognize, or build them with the DATE function to be safe.

You also need a clear definition of what counts as a holiday for you: fixed-date holidays such as Christmas, or floating ones such as Labor Day. Keep both kinds in the same list with a name column next to the date column. For a ready-made yearly layout to paste your list into, see how to create a calendar in Excel.

TODAY and MIN find next holiday

Use Word, Excel, and PPT for FREE

Free Download

How to Find the Next Holiday in Excel with MIN and TODAY

The core idea is simple: keep only the dates that have not passed yet, then take the smallest one. Suppose holidays are named in A2:A10 and dated in B2:B10:

1. Enter the array formula =MIN(IF(B2:B10>=TODAY(),B2:B10)) in an empty cell. In older Excel versions confirm it with Ctrl+Shift+Enter; in modern versions and WPS Spreadsheet a plain Enter works.

   

   
   

2. Format the result as a date. If the cell shows a large number such as 46310, open Home > Number Format and choose a date style.

   

   
   

3. Pull the holiday name with =INDEX(A2:A10,MATCH(MIN(IF(B2:B10>=TODAY(),B2:B10)),B2:B10,0)), which looks up which holiday owns that date.

   

   
   

4. Prefer a non-array option? Use =MINIFS(B2:B10,B2:B10,">="&TODAY()), which filters for dates on or after today without Ctrl+Shift+Enter.

How to Calculate the Next Holiday That Falls on an Nth Weekday

Many holidays do not sit on a fixed date; they land on the first Monday of a month, the third Monday, the fourth Thursday, and so on. You can generate those dates for any year with DATE, WEEKDAY, and a small offset. For Labor Day 2026, the first Monday of September:

=DATE(2026,9,1)+IF(WEEKDAY(DATE(2026,9,1),2)>1,7-WEEKDAY(DATE(2026,9,1),2)+1,1-WEEKDAY(DATE(2026,9,1),2))

Here is how the formula thinks, and how to bend it to other holidays:

1. Anchor on the first of the month with DATE(2026,9,1), then measure which weekday that day is using WEEKDAY(...,2), where Monday equals 1.

2. Add the offset that steps forward to the first Monday. Change the comparison value from 1 to 4 (with the same adjustment) to hunt for the first Thursday, as Thanksgiving needs.

3. Reach the nth weekday by adding (n-1)*7 days: for Presidents’ Day, the third Monday of February, add 14 to the first-Monday result.

4. Point the year at a cell such as B2 instead of typing 2026, then one dropdown changes every calculated holiday in the sheet at once.

Fixed-date holidays like Christmas need no math at all—just DATE(2026,12,25). Movable holidays tied to moon phases, such as Easter or Lunar New Year, are out of reach for these functions; keep those as typed dates in your list.

Use Word, Excel, and PPT for FREE

Free Download

How to Build a Next Holiday Countdown Calendar

Once your list holds real dates, a countdown is one formula away. The number of days until a holiday in B2 is simply =B2-TODAY(), because Excel stores dates as serial numbers. Build the full view like this:

1. List every holiday for the year in columns A (name) and B (date), sorted or not—the formulas do not care about order.

2. Add a Days-left column with =IF(B2-TODAY()<0,"",b2-today())<>, so past holidays show blank instead of negative numbers, then fill down.

3. Fix the formatting if results appear as dates: select the column and set Home > Number Format > General.

4. Highlight the next holiday with conditional formatting, or select the Days-left column and apply Conditional Formatting > Data Bars for an instant visual countdown.

Because TODAY() recalculates every time the file opens, the countdown stays current without any maintenance. If you plan vacations around weekends too, add a second column that checks whether the holiday lands adjacent to a Saturday or Sunday. A printable monthly layout for the same list is covered in how to create an Excel calendar in WPS Office.

holiday countdown data bars Excel

The Best Free Alternative: WPS Office for Holiday Tracking in Excel

Everything in this guide works in Microsoft Excel, but you do not need a paid subscription to run it. WPS Spreadsheet is part of WPS Office, a free office suite that covers Writer, Presentation, and Spreadsheets, and it is built to feel instantly familiar to Excel users.

Here is why it is a strong pick for a next-holiday tracker:

1. Every formula from this article runs unchanged. MIN, IF, INDEX, MATCH, MINIFS, DATE, WEEKDAY, and array-entry behavior all match Excel, so you can copy the formulas above straight into a WPS sheet.

2. Full file compatibility. WPS Spreadsheet opens and saves .xlsx, .xls, .xlsm, and .csv files, so you can start the tracker in Excel at work and finish it in WPS at home without conversion warnings or broken dates.

3. Free on every platform. Native apps for Windows, macOS, Linux, Android, and iOS, plus browser-based office tools, so the same holiday file follows you from desktop to phone; sign in with a free WPS account and WPS Cloud keeps it in sync.

4. A template library built for this job. The built-in template store includes yearly and monthly calendars, holiday planners, countdown sheets, and vacation trackers—pick one, paste in your holiday list, and the formulas from the previous section drop straight in.

5. Extras that round out planning. The suite bundles a PDF toolkit for converting and editing documents, so you can export the finished countdown calendar as a clean PDF to share or print.

For anyone who checks dates on more than one device, or simply does not want to pay for a spreadsheet, WPS Office is the most complete free way to keep a next-holiday calendar running all year.

WPS Office English home screen

Use Word, Excel, and PPT for FREE

Free Download

FAQs

Why does my MIN formula return a large number instead of a date?

The calculation is right; the cell formatting is not. Dates are serial numbers, so 46310 is a real date. Select the cell and set Home > Number Format to a date style.

Does the array formula always need Ctrl+Shift+Enter?

Only in older Excel versions. Microsoft 365, Excel 2021, and WPS Spreadsheet handle =MIN(IF(...)) with a plain Enter. If you want to avoid the question entirely, use MINIFS.

Can I use this for holidays in any country?

Yes. The formulas do not care which holidays you list. Type your own country’s dates, or calculate nth-weekday holidays with the DATE and WEEKDAY approach shown above.

Can Excel calculate Easter or other moon-based holidays?

Not reliably with basic functions. Keep movable holidays as typed dates in your list; the countdown and next-holiday formulas will still work on them.

Do these formulas work in WPS Spreadsheet?

Yes, every formula and the conditional-formatting data bars behave the same in WPS Spreadsheet, and files saved there open in Excel without conversion.

Free Office Download

• Use Word, Excel, and PPT for FREE, No Ads.

• Edit PDF files with the powerful PDF toolkit.

• Microsoft-like interface. Easy to learn. 100% Compatibility.

• Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

Free Download

100% secure

Algirdas Jasaitis