With no input cells needed, enter =EOMONTH(TODAY(),0)-TODAY() to calculate the number of days from today to the last day of this month, not counting today. If the date is November 2, 2026, the formula returns 28 because November 30 minus November 2 is 28 days; add 1 if you want to count today as a remaining calendar day.
The basic month-end formula
The formula combines two Excel functions. TODAY() supplies the current date, and EOMONTH(date, months) returns the final date of a month a specified number of months before or after a starting date. The zero argument means the month containing today. Subtracting today’s date from that month-end date gives the number of midnight-to-midnight date changes remaining.
With no input cells required and Excel’s current date set to November 2, 2026, use =EOMONTH(TODAY(),0)-TODAY(); the result is 28. This is a live calculation, not a fixed countdown saved at the time you typed it. TODAY() updates when Excel recalculates the workbook, so the result ordinarily decreases as dates pass.
The same method works in Excel for Microsoft 365 on Windows, Excel for Mac, and Excel for the web. Type the formula into a cell and press Enter. If you use a semicolon rather than a comma as the argument separator in other software or regional settings, note that Excel’s separator can depend on your system’s regional configuration; the formula shown uses the standard comma syntax.
Why the answer may be one less than expected
Subtracting dates measures the interval between them. It does not count both the starting date and ending date as separate full days. For example, from November 2 to November 30 there are 28 elapsed days, even though the calendar shows 29 dates when you list November 2 through November 30 inclusively.
With no input cells required and the current date November 2, 2026, use =EOMONTH(TODAY(),0)-TODAY()+1 if your definition of remaining days includes today; the result is 29. This is useful for a daily checklist that treats any date on which work can still be done as one of the available days. It is not a different month-end calculation; it simply adds the starting date to the count.
Choose the rule that matches the question you are answering:
- Use the basic formula for elapsed days until the final date, excluding today.
- Add 1 when you want an inclusive count of calendar dates, including today.
- If a deadline expires at the start of the last day rather than at its end, confirm the actual deadline wording. A date-only countdown cannot represent every time-of-day rule.
Calculate from a date in a cell
A fixed reference date is often more useful than TODAY() when you are building a report, checking past dates, or calculating for a date other than the current one. Put a valid Excel date in A2. With the date November 2, 2026 in A2, enter =EOMONTH(A2,0)-A2 in another cell; it returns 28. The result is based on the date in A2 and will not change merely because the computer’s date changes.
For an inclusive count based on A2, keep the same layout and use =EOMONTH(A2,0)-A2+1. With November 2, 2026 in A2, this returns 29. You can copy the formula down a column: if the date is in A3 on the next row, Excel adjusts the reference to A3 automatically. For a formula you intend to copy sideways or use in a more complex sheet, a fixed reference such as $A$2 prevents the reference from shifting, but it is not necessary for a simple fill-down.
A date in Excel is stored as a serial number, so subtraction works even when the cell is displayed as a human-readable date. Make sure A2 contains a real date value rather than text that merely looks like a date. A quick check is to change the cell’s number format temporarily to General; a recognized date becomes a number. If the entry remains text, re-enter it in a date format Excel recognizes for your regional settings.
How EOMONTH handles different months
There is no need to hard-code the number of days in a month. EOMONTH calculates the correct final date for February, 30-day months, and 31-day months. It also handles leap years, so February 2028 ends on February 29 while February 2027 ends on February 28. This is safer than subtracting a guessed number such as 30 or using a list of month lengths.
The second EOMONTH argument is an offset in months. Zero means the end of the current month; 1 means the end of next month; -1 means the end of last month. For example, a forecast that needs the end date two months after the date in A2 can use EOMONTH with an offset of 2. That returns a date, not a number of days, so subtract A2 if the goal is an elapsed-day count. The basic formula avoids that extra step by using an offset of zero.
Because the formula uses actual dates, it counts calendar days, including weekends and holidays. A month-end on a Saturday is still the month-end; EOMONTH does not move it to Friday or Monday. If your business process defines a different cutoff, such as the last working day, that is a separate calculation and should be specified explicitly.
Calendar days versus working days
People sometimes use days until month-end to plan staffing, shipping, or business reporting. The basic subtraction formula counts every calendar day. It does not exclude Saturdays, Sundays, company holidays, or public holidays. A result of 28 calendar days therefore does not mean 28 working days.
For a workday count, use the NETWORKDAYS function, which counts workdays between two dates and by default excludes Saturday and Sunday. With no holiday list and the current date November 2, 2026, enter =NETWORKDAYS(TODAY()+1,EOMONTH(TODAY(),0)) to count working dates after today through month-end; the result is 20. November 2, 2026 is a Monday, so the dates November 3 through November 30 contain 20 weekdays. Starting at TODAY()+1 makes the count exclude today, consistent with the basic elapsed-days interpretation.
If holidays are listed in H2:H10, and the same November 2, 2026 example applies, use =NETWORKDAYS(TODAY()+1,EOMONTH(TODAY(),0),$H$2:$H$10). The result is 20 less any listed holiday dates that fall on weekdays from November 3 through November 30; for example, if H2 contains one such weekday holiday and the other holiday cells do not contain dates in the interval, the result is 19. Keep the holiday range free of unrelated dates and text that could be mistaken for dates. NETWORKDAYS also counts the ending date if it is a workday.
Make the result display as a number
The answer is a number of days, but Excel may display it unexpectedly if the result cell has a date format. Date serial values are numbers internally, and a date format tells Excel to show that number as a calendar date. If the cell shows a date instead of a count, select it and choose Home > Number Format > General or Number. In Excel for Mac, the Home tab also has the Number Format controls; the exact appearance can vary with the window size. Excel for the web provides number-format options on its Home ribbon as well.
A result such as 28 is usually best shown with zero decimal places. If the formula returns a decimal, inspect whether the source date includes a time. Excel stores times as fractions of a day, so a date-time value can produce a partial-day difference. For a month-end countdown intended to use whole calendar dates, remove the time portion from the source or use a date-only value. Avoid formatting the answer as a date merely to make it look cleaner; that changes its display, not the underlying calculation.
Common errors and ways to troubleshoot
If Excel shows a formula error or an unexpected result, check the input and calculation behavior before replacing the formula. These are common causes:
- The answer is one day short: The basic formula excludes today. Add 1 only if an inclusive date count is intended.
- The result looks like a date: Change the result cell to General or Number format.
- The formula returns an error: Check that the referenced date is valid and that parentheses and argument separators match your Excel settings.
- The result does not update: TODAY() recalculates when Excel recalculates. Check Formulas > Calculation Options and ensure calculation is set to Automatic if you expect automatic updates.
- The formula returns a decimal: The source may contain a time as well as a date. Use a date-only value if you need whole days.
- A blank date cell gives a strange answer: Blank cells used in date arithmetic can be treated like zero. Add a check for missing input in a production sheet rather than assuming every row contains a date.
For a countdown visible to someone opening the file later, remember that TODAY() is based on the date Excel recognizes when the workbook recalculates. A workbook left open across midnight may not refresh at the exact instant the date changes in every calculation situation. Recalculation or reopening the workbook updates volatile date formulas. If you need an audited date that must never change, enter and preserve a fixed date instead of using TODAY().
When to use a different approach
Use the EOMONTH subtraction formula for calendar days to the calendar month’s final date. It is compact, handles month lengths automatically, and is clear about the endpoint. Use NETWORKDAYS when the question is about weekdays or workdays, and supply a holiday list when those dates should be excluded. Use a date in a cell rather than TODAY() when you need repeatable calculations for a specified date or historical reporting.
For a due date that should be the first day of next month, the target is not the last day of this month, even though the interval is closely related. With no input cells and the current date November 2, 2026, =EOMONTH(TODAY(),0)+1-TODAY() returns 29 days until December 1. The basic month-end formula returns 28 because November 30 is one day earlier. Distinguishing the target date prevents off-by-one misunderstandings in billing cycles, subscriptions, and monthly close schedules.
Excel for Microsoft 365 includes EOMONTH, TODAY, and NETWORKDAYS, and the same formulas are available in current desktop and web versions. No add-in or macro is needed. If you share a workbook with users of older spreadsheet programs, confirm that their program supports the functions and that their regional date settings interpret the input dates consistently.
Frequently Asked Questions
Does the formula count today?
No. =EOMONTH(TODAY(),0)-TODAY() returns the elapsed calendar days from today to month-end, excluding today. Add 1 for an inclusive count that includes today.
How do I count business days until the end of the month?
Use NETWORKDAYS with tomorrow as the starting date and EOMONTH(TODAY(),0) as the ending date to exclude today. Add a holiday range as the third argument if applicable. The function excludes weekends by default, but not holidays unless you provide them.
Will this formula work in February and leap years?
Yes. EOMONTH determines the actual final date of the month, so it handles February 28, February 29 in leap years, and months with 30 or 31 days without a manual adjustment.
Why does Excel show a date instead of the number of days?
The result cell is likely formatted as a date. Set its number format to General or Number. The calculation is still a numeric difference; the format controls how Excel displays it.
How can I calculate days until month-end for a date in another cell?
Put the date in A2 and use =EOMONTH(A2,0)-A2 in the result cell. It calculates for A2 rather than the current date, and adding 1 makes the count inclusive.
Related
- Replace Excel Formatting: Find and Change Cell Styles
- Replace Data in Excel: Find and Replace Guide
- Excel Data Analysis: What It Can Do and Where It Falls Short
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Count Unique Records in Excel: Formulas and PivotTablesTo count distinct values in a column in Microsoft Excel for Microsoft 365, put this formula in an…
- Excel Formula for Cylinder Volume: A Clear ExampleFor a cylinder with its radius in cell A2 and its height in B2, enter =PI()*A2^2*B2 in another…
- Excel SUM Ignore Errors: Formulas for Clean TotalsYes. To sum numbers while treating error cells as zero in Microsoft Excel for Microsoft 365, use =SUM(IFERROR(B2:B6,0));…
- Excel Text Length: Character Limits and the LEN FunctionExcel cells can contain up to 32,767 characters, and the LEN function counts the characters in a cell’s…
- Advanced Excel HLOOKUP Formula: Examples and TipsFor an advanced Excel HLOOKUP, use =HLOOKUP(F1,$A$1:$D$4,2,FALSE) when the value to find is in F1, the lookup keys…
- Excel Function to Remove Text from a StringExcel does not have one universal “remove text” function: use SUBSTITUTE to remove specified characters or words, REPLACE…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.