Excel Formula for Days Left in Month

Formulas & functions · Updated November 22, 2026

The simplest Excel formula for days left in the current month is =EOMONTH(TODAY(),0)-TODAY(). This returns the number of days from today until the last day of the month (excluding today). For a date in a specific cell, use =EOMONTH(A1,0)-A1.

How the Formula Works

Excel’s EOMONTH (end of month) function takes two arguments: a start date and a number of months. EOMONTH(TODAY(),0) returns the serial number of the last day of the current month. Subtracting TODAY() gives the difference in days. Because dates in Excel are stored as serial numbers (1 for January 1, 1900), subtracting two dates yields the number of days between them. The result is an integer; for example, if today is March 15, 2025, EOMONTH(TODAY(),0) returns March 31, and the formula returns 16.

Using a Specific Date Instead of Today

If you need days left in the month for a date entered in a cell, replace TODAY() with the cell reference. Assuming the date is in A1, the formula is =EOMONTH(A1,0)-A1. This works for any date, past or future. For instance, with A1 containing July 4, 2025, the result is 27 (July has 31 days). To include today in the count, add 1: =EOMONTH(A1,0)-A1+1. This is useful for countdowns or schedules where the current day counts as a remaining day.

Handling Blank Cells and Errors

If A1 is empty, EOMONTH returns a #VALUE! error because an empty cell is treated as zero (January 0, 1900). To avoid this, wrap the formula in an IF check: =IF(A1="","",EOMONTH(A1,0)-A1). For dates stored as text, use DATEVALUE first: =EOMONTH(DATEVALUE(A1),0)-DATEVALUE(A1). Also note that TODAY() is volatile—it recalculates every time the workbook opens or changes. If you need a static value, copy the result and paste as values (Ctrl+Shift+V on Windows, Cmd+Shift+V on Mac).

Alternative: Using DAY and DATE Functions

You can achieve the same result without EOMONTH using DATE and DAY. The formula =DATE(YEAR(A1),MONTH(A1)+1,1)-A1 calculates the first day of the next month and subtracts the date, giving days left. This is more verbose but works in older Excel versions (pre-2007) where EOMONTH might require the Analysis ToolPak. Another variant: =DAY(EOMONTH(A1,0))-DAY(A1) gives the difference between the last day number and the current day number, but this fails if A1 is the last day (returns 0 instead of 0, which is correct, but if A1 is the first day, it returns the month length minus 1, which is correct). The EOMONTH subtraction is cleaner and less error-prone.

Common Mistakes and Misconceptions

A frequent error is using =EOMONTH(A1,1)-A1 thinking it gives days left in the current month; actually, the second argument is the number of months to add, so 1 gives the end of next month. Another mistake is forgetting that TODAY() updates automatically—if you need a snapshot for a report, use a static date. Also, some users expect the result to include the current day; the standard formula excludes it. If you want to count today as a remaining day, add 1. Finally, be aware that EOMONTH returns a date serial number; if you format the cell as a date, it will display the last day of the month, not the number of days. Change the cell format to Number to see the day count.

Real-World Examples and Variations

For project tracking, you might want to calculate days left in the month for a list of dates in column A. Place this in B2 and drag down: =IF(A2="","",EOMONTH(A2,0)-A2). To highlight rows where days left are less than 5, use conditional formatting with a formula rule: =EOMONTH($A2,0)-$A2<5 applied to the range. If you need to calculate days left in a quarter, replace EOMONTH with DATE(YEAR(A1),CEILING(MONTH(A1)/3,1)*3+1,1)—but that's more complex. For a simple countdown to month end, you can also use =DATEDIF(TODAY(),EOMONTH(TODAY(),0),"d"), which returns the same number but is longer. The DATEDIF function is undocumented in modern Excel but still works.

Frequently Asked Questions

Does the formula work in Google Sheets? Yes, EOMONTH and TODAY exist in Google Sheets with the same syntax. The only difference is that Google Sheets uses a different date serial system (December 30, 1899 as day 0), but the subtraction still works correctly.

How do I get days left in the month for a date in the past? The formula works for any date. For example, if A1 contains January 15, 2020, the result is 16 (January has 31 days). It doesn't matter if the date is in the past; the math is the same.

Why does my formula return a date instead of a number? Because the cell is formatted as a date. Select the cell, go to Home > Number Format, and choose General or Number. The underlying value is correct; only the display is off.

Can I use this to calculate days left in a month for multiple months ahead? Yes, change the second argument in EOMONTH. For example, =EOMONTH(TODAY(),1)-TODAY() gives days left until the end of next month. For a specific month offset, use =EOMONTH(TODAY(),n)-TODAY() where n is the number of months ahead.

What if I want to exclude weekends? The basic formula counts all days. To count only weekdays, use =NETWORKDAYS(TODAY(),EOMONTH(TODAY(),0)). This excludes Saturdays and Sundays by default. To exclude holidays, add a holiday range as a third argument, e.g., =NETWORKDAYS(TODAY(),EOMONTH(TODAY(),0),H2:H10).

Performance and Volatility Notes

Because TODAY() is volatile, using it in thousands of cells can slow down workbook recalculation. If you have a large dataset, consider placing =TODAY() in a single cell (e.g., Z1) and referencing that: =EOMONTH($Z$1,0)-$Z$1. This reduces recalculation overhead. Also, if you're using Excel for the web or Mac, the formulas work identically; no ribbon differences apply. For Excel 2021 and Microsoft 365, you could use LET to define a variable: =LET(d,TODAY(),EOMONTH(d,0)-d), which is cleaner if you repeat the date reference multiple times.

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.