Excel Formula for Calendar Days in a Month

Formulas & functions · Updated November 22, 2026

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

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.