The simplest Excel formula for calendar days in a month is =DAY(EOMONTH(A1,0)), where A1 contains any date within that month. This returns the number of days (28, 29, 30, or 31) for the month of the date in A1.
How the Formula Works
EOMONTH(A1,0) returns the last day of the month for the date in A1. The second argument, 0, means “same month”; use 1 for next month, -1 for previous. Then DAY() extracts the day-of-month number from that end-of-month date. For example, if A1 contains 15-Mar-2025, EOMONTH(A1,0) gives 31-Mar-2025, and DAY() returns 31.
This works for any date, including those with time components, because Excel stores dates as serial numbers with time as a decimal fraction. The formula ignores the time part automatically.
Alternative Formulas for Specific Months
If you know the month and year as numbers, use DATE(year, month+1, 0). The zero day of the next month is the last day of the current month. For example, with year in B1 and month in C1: =DAY(DATE(B1, C1+1, 0)). If C1 is 12 (December), DATE(2025, 13, 0) becomes 31-Jan-2026, correctly giving 31.
Another approach uses EOMONTH with a constructed date: =DAY(EOMONTH(DATE(B1,C1,1),0)). This is more explicit but longer. The DATE function method is shorter and avoids the extra EOMONTH call.
Handling Blank Cells and Errors
If A1 is blank, EOMONTH(A1,0) returns 0 (Excel’s date for 0-Jan-1900), and DAY(0) returns 0. To show a blank instead, wrap it: =IF(A1="","",DAY(EOMONTH(A1,0))). If A1 contains text that isn’t a date, you’ll get a #VALUE! error. Use =IF(ISNUMBER(A1),DAY(EOMONTH(A1,0)),"") to avoid that.
For a month name in text like “March 2025”, use =DAY(EOMONTH(DATEVALUE("1 "&A1),0)) but only if the text is a valid date format in your regional settings. Better to use =DAY(EOMONTH(A1,0)) after converting the text to a real date with DATEVALUE in a helper column.
Using the Formula in Larger Calculations
This formula is often used to calculate pro-rated amounts, daily rates, or to create dynamic date ranges. For instance, to get the first day of the month: =EOMONTH(A1,-1)+1. To get the last day: =EOMONTH(A1,0). To count weekdays in a month, combine with NETWORKDAYS: =NETWORKDAYS(EOMONTH(A1,-1)+1, EOMONTH(A1,0)).
For a monthly total from a daily rate, multiply: =B1*DAY(EOMONTH(A1,0)) where B1 is the daily rate. This is common in budgeting templates.
Leap Years and February
The formula automatically handles leap years. For February 2024 (a leap year), EOMONTH(DATE(2024,2,1),0) returns 29-Feb-2024, so DAY gives 29. For 2025, it gives 28. You never need a separate leap-year check because Excel’s date engine knows the calendar.
If you need to know whether a year is a leap year, use =IF(MOD(YEAR(A1),4)=0, IF(MOD(YEAR(A1),100)=0, MOD(YEAR(A1),400)=0, TRUE), FALSE) but it’s rarely necessary when the DAY/EOMONTH formula already gives you the correct count.
Common Mistakes and Fixes
- Using DAY(A1) – this returns the day of the date itself (e.g., 15 from 15-Mar), not the number of days in the month.
- Using DAY(DATE(YEAR(A1),MONTH(A1),31)) – this fails for months with 30 days because Excel rolls over to the next month (e.g., 31-Apr becomes 1-May).
- Forgetting the comma in EOMONTH –
EOMONTH(A1 0)is invalid syntax. - Assuming the formula returns a date – DAY returns a number, so format the cell as General or Number, not as a date.
Frequently Asked Questions
Does this work in Google Sheets?
Yes, =DAY(EOMONTH(A1,0)) works identically in Google Sheets. The only difference is that Sheets uses the same function names and syntax. If you’re using Excel for the web or Mac, the formula is the same; no version differences exist for these functions.
How do I get the number of days for a month name like “March”?
If you have a text month name in A1, use =DAY(EOMONTH(DATEVALUE("1 "&A1&" 2025"),0)) but this requires a year. Better to have a date in a cell and reference it. If you only have a month number (1-12) in A1 and a year in B1, use =DAY(DATE(B1,A1+1,0)).
Why does my formula return 31 for February?
That happens if you used DAY(A1) where A1 contains a date like 31-Jan, or if you used DAY(DATE(YEAR(A1),MONTH(A1),31)). The correct formula =DAY(EOMONTH(A1,0)) will never return 31 for February.
Can I use this to create a dynamic list of all days in a month?
Yes. In Excel 365, use =SEQUENCE(DAY(EOMONTH(A1,0)),1,EOMONTH(A1,-1)+1,1) to generate a vertical array of dates from the 1st to the last day. This spills automatically. In older versions, you’d need a helper column with ROW and IF formulas.
What if my date is in a different system (e.g., 1904 date system)?
The formula still works. Excel’s 1904 date system (used on old Mac files) shifts serial numbers, but EOMONTH and DAY are system-agnostic. You don’t need to change anything.
Is there a formula that doesn’t require a date cell?
Yes, if you have the year and month as numbers, =DAY(DATE(year, month+1, 0)) is the shortest. For example, =DAY(DATE(2025, 3+1, 0)) returns 31 for March 2025.
Related
- Excel Calendar Template for Events: How to Build One
- Excel Business Template for Invoice: How to Use
- Excel Business Template for Profit and Loss
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Insert Image from URL: Formula, Steps, and FixesTo insert an image from a URL in Microsoft Excel for Microsoft 365, put the direct image address…
- How to Make a Function in C++ (Step-by-Step)In C++, a function is a reusable block of code that performs a specific task. You call it…
- Excel Quadratic Formula: Solve Equations in a SheetYes. Excel can calculate the roots of a quadratic equation with the quadratic formula: enter the coefficients a,…
- Excel OFFSET Function: How to Use It in Formulas and RangesIn Excel for Microsoft 365 on Windows, OFFSET returns a reference moved a chosen number of rows and…
- Identify Offsetting Entries in Excel With Formulas and ChecksTo flag entries with an equal and opposite amount, put the date in column A, account in B,…
- How to Filter by Month in Excel Using Dates or TextTo filter month-wise in Excel, select a cell in your data, choose Data > Filter, open the date…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.