How Many Days in a Month: Excel Formula

To count the days in a month in Excel, use =DAY(EOMONTH(A2,0)), where A2 is any date in that month. It returns 28, 29, 30 or 31 and handles leap years for you. This guide explains how the formula works, then shows the variations people need for budgets, rotas and reports: the current month, a month and year typed as numbers, days left in the month and working days.

All the examples use these dates in column A:

The days in month formula#

The formula has two parts. In B2:

=DAY(EOMONTH(A2,0))

1. EOMONTH(A2,0) returns the end of the month: the last day of the month that A2 is in. For 10 Feb 2024 that is 29 Feb 2024. The 0 means "this month". A 1 would mean next month and a -1 last month.
2. DAY(...) returns the day number of that date, from 1 to 31. The last day of a month is always the same as the number of days in it, so the result is 29.

Fill the formula down (Windows: Ctrl+D; Mac: Cmd+D) to get 28, 30, 31 and 31 for the other rows. Note that 2024 is a leap year and 2025 is not, and the formula gets both right without any extra check. The EOMONTH function guide lists its arguments.

How to get the days in the current month#

Replace the cell with TODAY to always get the current month:

=DAY(EOMONTH(TODAY(),0))

Because TODAY changes every day, the result updates itself when a new month begins. This is useful in dashboards that show "day 12 of 30" or a daily target.

Days in a month from a month and year number#

Sometimes you don't have a date, only a year in B2 (2025) and a month number in C2 (2). Build the date with DATE instead:

=DAY(DATE(B2,C2+1,0))

This uses a handy DATE trick: day 0 of a month means the last day of the month before. So DATE(2025,3,0) is 28 Feb 2025, and DAY returns 28. It works for December too: DATE(2025,13,0) rolls over to 31 Dec 2025.

You could also write =DAY(EOMONTH(DATE(B2,C2,1),0)). Both give the same answer, so use whichever you find easier to read.

If the month is text, such as "Feb 2025", turn it into a date first with DATEVALUE: =DAY(EOMONTH(DATEVALUE("1 "&A2),0)). How DATEVALUE reads text depends on your computer's date settings, so test it on your own data. To go the other way and show a month name, see how to get the month name from a date.

How many days are left in the month?#

Subtract the date from the last day of its month:

=EOMONTH(A2,0)-A2

For 10 Feb 2024, the result is 29 − 10 = 19 days left after today. Add 1 if you want to count the date itself: =EOMONTH(A2,0)-A2+1 gives 20. If the answer appears as a date, change the format to General.

The opposite question, "how many days have passed?", is simply =DAY(A2), which gives 10. Together they power month-to-date reports, covered in the guide to month-to-date totals.

How many working days are in the month?#

For working days (Monday to Friday), give NETWORKDAYS the first and last day of the month:

=NETWORKDAYS(EOMONTH(A2,-1)+1,EOMONTH(A2,0))

EOMONTH(A2,-1)+1 is the day after the end of last month, which is the 1st of this month. For February 2024, the result is 21 working days. Add a list of public holidays as a third argument to leave those out too. The first and last day of the month guide explains these month-boundary formulas in more detail.

Example: split a monthly cost by days#

Days in the month is often a step in a bigger formula. Say a tenant moves in on 19 Apr 2025 (A2) and the monthly rent is 1,200 (C2). They pay for 19 to 30 April, which is 12 days:

=C2/DAY(EOMONTH(A2,0))*(EOMONTH(A2,0)-A2+1)

That is 1,200 / 30 × 12 = 480. The same pattern spreads a monthly budget into a daily figure, or works out a part-month salary. To shift dates by whole months instead, see how to add days, months and years. To total values for each month, see how to count by month.

How to list the days in every month of a year#

In Excel 2021, Excel 2024 or Microsoft 365, SEQUENCE can build all twelve months at once:

=DAY(EOMONTH(DATE(2025,SEQUENCE(12),1),0))

SEQUENCE(12) makes the numbers 1 to 12, so DATE builds the 1st of each month, and the formula spills 12 results down the column: 31, 28, 31, 30 and so on. Swap 2025 for 2024 and the second value becomes 29. That makes it a quick leap year check too: =DAY(EOMONTH(DATE(B2,2,1),0))=29 returns TRUE when the year in B2 is a leap year.

Common mistakes#

• The answer looks like a date. If B2 had a date format, 29 shows as 29/01/1900. Change the format to General (Windows: Ctrl+Shift+; Mac: Ctrl+Shift+).
• The date is text. A date typed as text, often aligned left, makes EOMONTH return #VALUE!. Convert it with DATEVALUE or re-enter it.
• Forgetting the 0. EOMONTH needs two arguments. =EOMONTH(A2) is an error.
• Typing 30 for every month. Hard-coded numbers break in February and 31-day months. Let the formula work it out.

Practise it#

Practise EDATE, EOMONTH and WEEKDAY in a live spreadsheet: you type each date formula and it checks your answer instantly. Start with the February 2024 exercise on this page, then fill the formula down to see 28, 30 and 31.