Days Left in Month in Excel: Formula and Examples for Dates

Formulas & functions · Updated November 22, 2026

If the date is in A2, use =EOMONTH(A2,0)-A2 to count the days remaining after that date, not including the date itself. For example, if A2 contains January 15, 2025, the result is 16; add +1 if you want to count January 15 as a day left too.

The basic formula and what it counts

The formula works by finding the final date of the month containing A2, then subtracting the date in A2. EOMONTH(A2,0) returns the month-end date for the month that A2 falls in. Subtracting A2 from that date gives the number of calendar days between the two dates. Excel stores valid dates as serial numbers, so subtracting one date from another returns a whole number of days.

This calculation uses the common “days after the selected date” convention. If A2 is April 10, the result is 20: April 11 through April 30. If A2 is April 30, the result is 0, because there are no later dates in April. The zero does not mean that April 30 is not a date in the month; it means there are no days remaining after it.

To count the selected date as well, use this version. With the date in A2, =EOMONTH(A2,0)-A2+1 returns 21 for April 10, counting April 10 through April 30. On April 30 it returns 1.

Calculate days left in the current month

If the goal is to show how many days remain in the current month and there is no date input cell, use the current date supplied by Excel’s TODAY function. =EOMONTH(TODAY(),0)-TODAY() returns the number of days after today through the last day of this month. For a count that includes today, use =EOMONTH(TODAY(),0)-TODAY()+1.

TODAY updates when Excel recalculates the workbook; it is not a permanently recorded timestamp. That makes it useful for a live dashboard, a monthly checklist, or a status cell that should keep counting down. It also means the displayed result can change when the workbook is opened on another day. If you need to preserve the number for a report or historical record, copy the result and use Paste Special > Values, or record the date used for the calculation alongside the result.

Excel normally recalculates formulas automatically. If the TODAY result appears not to change, check Formulas > Calculation Options > Automatic. A manual-calculation workbook may need a recalculation before the displayed value updates. TODAY uses the date recognized by the computer and Excel environment; it does not include a time-of-day component in this calculation.

Choose whether today counts

“Days left” can mean either the number of full calendar days after a date or the number of calendar dates remaining when the current date is included. Excel cannot determine which meaning a schedule, deadline, or report expects, so choose the convention before building the formula.

  • Exclude the starting date: use =EOMONTH(A2,0)-A2. This is useful for a countdown to a future date or for counting days after a completed date.
  • Include the starting date: use =EOMONTH(A2,0)-A2+1. This is often easier for a checklist that treats today as an available day.
  • Use the current date: replace A2 with TODAY() to make the result update automatically.

Be consistent when comparing this number with another calculation. For example, if you count today as a day left in the month but calculate a deadline interval without including its start date, the figures may differ by one. That is an inclusive-versus-exclusive counting choice, not necessarily a formula error.

Examples across different months

These examples use real Excel dates in A2 and the exclusive formula, which counts days after the date entered. The results are calendar-day counts, not working-day counts.

  • January 15, 2025: January ends on January 31. The difference is 16 days, so the formula returns 16.
  • February 10, 2024: 2024 is a leap year and February ends on February 29. The formula returns 19 days after February 10.
  • February 10, 2025: February ends on February 28. The formula returns 18 days after February 10.
  • April 30, 2025: The selected date is already the final day of its month, so the exclusive formula returns 0. The inclusive version returns 1.
  • December 20, 2025: The month-end date is December 31, so the result is 11 days after December 20. The formula does not roll into January because it finds the end of the month containing the input date.

The February examples show why it is safer to calculate month-end than to assume every month has 30 or 31 days. EOMONTH handles short months, leap years, and year boundaries for you. It also means the input can be any valid date in the month; the formula does not need a separate rule for each month.

Set up a date list and keep blank rows clean

For a simple table, put dates in column A and the formula in B2. With the date in A2, enter =EOMONTH(A2,0)-A2 in B2, then fill the formula down for the rest of the rows. In Excel for Microsoft 365 on Windows, you can drag the fill handle at the lower-right corner of B2 down the column, or copy B2 and paste it into the target cells. Excel adjusts A2 to A3, A4, and so on as the formula is copied.

If the date column includes empty rows, a blank-safe version prevents a blank input from being treated as Excel’s zero date. With dates in A2:A100 and the result formula in B2, use =IF(A2="","",EOMONTH(A2,0)-A2), then fill down. This leaves B blank when A is blank. If A2 contains text that is not a recognized date, the calculation can still return an error; the input should be a genuine Excel date value.

Dates imported from another system may look like dates while actually being text. A quick check is to select the cell and change its number format: a real date usually displays as a serial number under General, while text remains text. You can also test a sample with =ISNUMBER(A2); it returns TRUE for a date stored as an Excel number. If the import is text, convert it to dates before relying on date arithmetic, taking care that the source’s day-month order matches your regional settings.

Display and use the result correctly

The result is a number of days, so format the result cell as General or Number rather than Date. If Excel displays an unexpected date such as a day in 1900, the calculation may be correct but the result cell may have a date format. Select the result cells and choose Home > Number Format > General, or choose Number if you prefer a numeric display. The menu labels are substantially the same in Excel for Mac, though ribbon layout can vary by window size.

Once the result is a number, you can use it in other formulas, sort a table by the number of days remaining, or apply conditional formatting to draw attention to dates near month-end. Remember that the formula counts every calendar day, including Saturdays, Sundays, and holidays. For a working-day countdown, a different calculation is needed, and you must decide how weekends and holidays should be treated.

If you want a sentence rather than a numeric value, combine a number with a label only after deciding how blanks should appear. For most analysis, it is better to keep the result numeric in its own column: numeric results can be summed, filtered, sorted, and compared more reliably than text such as “16 days left.” You can add a separate display column if a report specifically needs wording.

Common errors and how to resolve them

  • The result is one day too high or low: decide whether the input date should count. Add 1 to include it; leave the formula unchanged to count only later dates.
  • The result is a date rather than a number: change the result cell’s format to General or Number. Date formatting changes how the number is displayed, not the calculation itself.
  • The formula returns #VALUE!: check whether the input is text, a malformed date, or a cell containing an unexpected value. Convert valid date text to a true date before using EOMONTH.
  • A blank input produces an odd result: use the IF version above to return a blank when A2 is empty.
  • The current-month result does not appear to update: check that calculation is set to Automatic, or recalculate the workbook. TODAY changes as Excel recalculates rather than acting as a running clock.
  • Excel rejects a formula’s commas: some regional settings use semicolons as formula separators. The formulas here use the standard comma syntax; if Excel expects semicolons on your installation, substitute the separator while keeping the function and arguments the same.

When a different calculation is appropriate

EOMONTH is a good choice when the question is specifically about the calendar month containing a date. If you instead need the number of days until a separate deadline, subtract the start date from the deadline date. If you need the number of business days remaining, use a working-day calculation rather than treating every calendar day as a workday. Those are different questions, so replacing the month-end formula without clarifying the requirement can produce a misleading result.

For a simple monthly countdown, EOMONTH is often clearer than manually constructing the final date with month numbers or adding a fixed number of days. It also avoids errors in February and in months with 30 days. In Microsoft 365, it is not necessary to use a newer dynamic-array function for this task; the formula works as a regular single-cell calculation and can be filled down a conventional table.

Frequently Asked Questions

How do I count today as a day left in the month?

Add 1 to the result. With a date in A2, use =EOMONTH(A2,0)-A2+1. For the current date, use =EOMONTH(TODAY(),0)-TODAY()+1.

What formula shows days left in the current month?

Use =EOMONTH(TODAY(),0)-TODAY() to count dates after today through the end of this month. Add 1 if your definition includes today.

Why does the formula return zero on the last day of the month?

The basic formula excludes the starting date and counts only dates after it. There are no later dates in that month when the input is the final day, so the result is 0. Add 1 to count the final day itself.

Does this formula account for weekends and holidays?

No. It counts calendar days, including weekends and holidays. If you need working days, use a business-day calculation and provide the holiday dates that should be excluded.

Will the formula work in older Excel versions?

EOMONTH is available in modern Excel versions, including Microsoft 365. Some older versions may require the Analysis ToolPak add-in for EOMONTH; if Excel does not recognize the function, check the version and available add-ins. The date-subtraction logic itself is standard Excel arithmetic.

Related

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.