Excel: How Many Days Are in a Month? Formula Guide

Formulas & functions · Updated November 18, 2026

A month can have 28, 29, 30, or 31 days, so Excel needs a year and month—or a date within the month—to give the right answer. If A2 contains any valid date in the month you want to check, enter =DAY(EOMONTH(A2,0)) to return that month’s number of days.

The quick answer: use EOMONTH and DAY

The formula combines two functions. EOMONTH finds the last day of the month, and DAY extracts the day number from that date. Since the last day of a month is also its total number of calendar days, the result is the answer you need. For example, if A2 contains a date in April 2026, the formula returns 30; if it contains a date in May 2026, it returns 31.

For a concrete check, with A2 containing the Excel date 15-Feb-2024, enter =DAY(EOMONTH(A2,0)) in B2. B2 returns 29, because 2024 is a leap year. The day of the date in A2 does not matter: the 1st, 15th, and 28th of the same month all produce the same month-length result.

Use a real Excel date in A2, not just a month name such as February. You can type a date such as 2/15/2024, or build one with Excel’s DATE function. How Excel interprets a typed date can depend on your Windows regional settings, so a date that looks valid may be read differently in another locale.

Why months have different lengths

The Gregorian calendar used for ordinary civil dates has months of differing lengths. January, March, May, July, August, October, and December have 31 days. April, June, September, and November have 30. February normally has 28, and has 29 in a leap year. Excel follows those calendar rules when it calculates dates; there is no single fixed number of days in a month.

A leap year has an extra day in February. Under the Gregorian rule, a year divisible by 4 is usually a leap year, except that a year divisible by 100 is not one unless it is also divisible by 400. Thus 2024 is a leap year, 1900 was not, and 2000 was. In everyday spreadsheet work, it is usually safer to let Excel calculate the date rather than try to encode this rule yourself.

This distinction matters in schedules, billing models, inventory reports, and monthly data summaries. A formula that assumes every month has 30 days can be wrong by one, two, or three days depending on the month. If the task is to count calendar days in a particular month, use the actual year as well as the month so February is handled correctly.

How to enter and check the formula

In Excel for Microsoft 365 on Windows, place a date from the target month in A2, select another cell such as B2, type the formula, and press Enter. The function arguments are separated by commas in the formula shown here. If your Excel regional settings use semicolons as list separators, Excel may require semicolons instead; the function names and calculation are otherwise the same.

  1. Enter a valid date from the month you want to measure in A2.
  2. In B2, enter =DAY(EOMONTH(A2,0)) and press Enter.
  3. Check the result against the month and year. For example, a date in February 2023 returns 28, while a date in February 2024 returns 29.

The formula returns a number, not a date. If B2 displays an unexpected date instead of a number, change its number format: select B2, then choose Home > Number Format and select General or Number. The underlying result is still the day number, but a date format can display that number as a calendar date.

EOMONTH is a standard worksheet function in Microsoft 365 for Windows. It can also be used in Excel for Mac and Excel for the web; the formula is the same, although ribbon layout and keyboard shortcuts can differ. The Windows Enter key confirms a formula. On Mac, press Return to confirm it.

Use separate year and month cells

You do not have to keep a complete date in the input cell. If A2 contains the year as a number, such as 2025, and B2 contains the month number, such as 2 for February, you can construct the first day of that month and find its final day. Put the result formula in C2:

With the year in A2 and the month number (1 through 12) in B2, enter =DAY(EOMONTH(DATE(A2,B2,1),0)) in C2. If A2 is 2025 and B2 is 2, C2 returns 28. If A2 is 2024 and B2 is 2, it returns 29.

DATE creates a date from the supplied year, month, and day. EOMONTH then moves zero months from that date to the last day of the same month, and DAY returns the day number. This setup is useful when a report has separate Year and Month columns or when a user selects a month from a list.

DATE can roll over values outside the usual month range: for example, month 13 is interpreted as January of the following year. That can be useful for date arithmetic, but it may conceal an input mistake in a form. If people will type month numbers manually, consider adding data validation to restrict entries to 1 through 12 and checking that the year is in the range your workbook needs.

An alternative formula and what it does

You can get the same result without EOMONTH by constructing the first day of the next month and asking Excel for day zero. Excel treats day zero as the final day of the preceding month. This is an established date-calculation technique, but the EOMONTH version often makes the intention easier to recognize.

With a valid date in A2, enter =DAY(DATE(YEAR(A2),MONTH(A2)+1,0)). For a date in February 2024, YEAR and MONTH supply 2024 and 2; DATE builds day zero of March, which resolves to February 29; DAY returns 29. The same method returns 28 for February 2023.

Choose either formula and use it consistently in a workbook. EOMONTH can also be handy when you need the actual final date—for example, to set a reporting cutoff—whereas the DATE version can be convenient if you are already building dates from year and month parts. Neither formula counts working days; both calculate calendar month length.

Common problems and how to fix them

If the answer is wrong, first check the input date and the cell’s value rather than the formula alone. A date that appears to be in one month may actually be stored as text, or may have been interpreted using a different date order. Select A2 and inspect the formula bar, or change its number format temporarily to a recognizable date format.

  • The formula shows an error: confirm that A2 contains a valid date and that the formula has matching parentheses. If your Excel uses semicolons as separators, replace the commas with semicolons.
  • The result looks like a date: set the result cell’s format to General or Number.
  • The February answer is unexpected: check the year as well as the month. February 2024 has 29 days; February 2025 has 28.
  • The result is always the same: make sure the formula refers to the intended input cell, particularly after copying it down a table.

When copying a formula down rows, a relative reference such as A2 changes to A3, A4, and so on. That is normally what you want if each row has a different date. If the date is stored in one fixed cell, use an absolute reference such as $A$2 so the reference does not move when the formula is copied. Check a few rows after filling down; a reference error can produce plausible-looking but incorrect results.

Calendar days, workdays, and days between dates

“How many days in a month?” can mean different things in a spreadsheet. The EOMONTH-and-DAY formula gives the number of calendar days in that particular month. It does not give the number of weekdays, business days, days remaining from today, or elapsed days between two dates. Those are separate calculations and should not be substituted for month length.

For example, February 2024 has 29 calendar days, but the number of Monday-to-Friday workdays depends on which weekdays its dates fall on and whether holidays are excluded. A workday count requires a date range and, when relevant, a holiday list. Similarly, subtracting one date from another measures elapsed days between those dates, not the full length of a calendar month.

For a monthly report, decide what the denominator represents before building a percentage. A daily rate based on actual calendar days may use 28, 29, 30, or 31; a rate based on scheduled working days should use the work schedule instead. These choices can materially affect a result, so label the measure clearly rather than naming both simply “days in month.”

Frequently Asked Questions

Does every month have 30 days in Excel?
No. Excel uses calendar dates, so months have 28, 29, 30, or 31 days. Use a date from the relevant month with =DAY(EOMONTH(A2,0)) to calculate the correct count.

How do I get the number of days in February?
Enter a date from February, including the year, in A2 and use =DAY(EOMONTH(A2,0)). It returns 28 in a common year and 29 in a leap year.

Can I calculate month length from a month number?
Yes. With the year in A2 and month number in B2, use =DAY(EOMONTH(DATE(A2,B2,1),0)). Use month numbers 1 through 12 for January through December.

Why does Excel show a date instead of 31?
The result cell may have a date number format. Select the cell and change its format to General or Number. The formula returns a numeric day count, but Excel can display numbers differently depending on cell formatting.

Does this formula count business days?
No. It returns all calendar days in the month, including weekends and holidays. A business-day count is a different calculation that needs the month’s date range and may also need a list of holidays.

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.