To get the number of days in a month, use =DAY(EOMONTH(A2,0)). EOMONTH with a 0 returns the last date of that month, and DAY pulls the day number from it — which is always the total days in the month.
For 31-Mar it returns 31, for February 2026 it returns 28, and for February 2028 it returns 29, so leap years are handled without any extra work. For the current month, use =DAY(EOMONTH(TODAY(),0)).
If you only have a month number and a year rather than a date, build the date inside it: =DAY(EOMONTH(DATE(2026,B2,1),0)) where B2 holds the month number. And for the days in a whole year, =DATE(YEAR(A2)+1,1,1)-DATE(YEAR(A2),1,1) returns 365 or 366.
Sometimes you need to calculate the total number of days in a month to use in further calculations. It happens specifically when you work on templates and dashboards. So today, let me show you a simple formula that can do this for you.
To calculate the number of days in a given month from a date, we need to use a formula based on EOMONTH and DAY.
Formula: Get Total Days in a Month
=DAY(EOMONTH(A2,0))
How This Formula Works
As you see, this formula is a combination of two functions. We have EOMONTH, which is wrapped inside DAY.
- First, when you refer to a date and “0” in EOMONTH, it returns the last date of that month. Here the date is in cell A2.
- Second, the DAY function returns the day number from the last date returned by EOMONTH.
The day number of the month’s last day is always the total number of days in that month. From 31-Mar-2017, it returns 31, which is the total number of days in March. It also handles leap years: for any date in February 2024, it returns 29.
Dynamic Formula
Let’s say you want to get the total days in the current month, and you want this formula to update automatically every time the month changes.
The formula will be:
=DAY(EOMONTH(TODAY(),0))
In this formula, instead of referring to a date, we have referred to TODAY in EOMONTH, which returns the current date, and then EOMONTH returns the last date of the current month.
In the end, DAY returns the day number, which is the total number of days in the current month.
Get Days in Month using Month Name
Let’s say you have entered a month’s name in a cell, not a proper date, and now you want to calculate the total number of days from this month. The formula will be:
=DAY(EOMONTH(DATEVALUE("01-"&A2&"-"&YEAR(TODAY())),0))
This formula creates the date of the first day of the month you have mentioned, then EOMONTH returns the last date, and DAY returns the last day, which is the total number of days in the month. You can use the month’s name both in short form and in long form.
As the formula uses the current year, February returns 28 or 29 depending on the year when you open the file. DATEVALUE also reads the text with your system’s date settings, so if you get a #VALUE! error, check that your Excel language understands the month names you have used.
Days in Month without EOMONTH
You can also get the same result with DATE. When you use 0 as the day for the next month, DATE goes back one day to the last day of the current month:
=DAY(DATE(YEAR(A2),MONTH(A2)+1,0))
This works the same way for December too, as DATE turns month 13 into January of the next year by itself. And if you have the year in B1 and the month number in C1, you don’t need a date at all:
=DAY(DATE(B1,C1+1,0))
Days Left and Working Days in a Month
To get the number of days left in the month after the date in A2, subtract the date from the last date of the month (format the result cell as General, not as a date):
=EOMONTH(A2,0)-A2
And to count only the working days (Monday to Friday) in the month of A2, use NETWORKDAYS from the first day to the last day of the month:
=NETWORKDAYS(EOMONTH(A2,-1)+1,EOMONTH(A2,0))
You can add a list of holidays as the third argument of NETWORKDAYS to leave them out as well.
Frequently Asked Questions
Why does the formula return #VALUE!?
The date in A2 is stored as text, not as a real date. Convert it to a date first (for example, with DATEVALUE or Data ➜ Text to Columns), and the formula works.
Why does the result show as a date?
The result cell has a date format. Change it to General or Number, and you see the number of days, like 31.
Does this formula work for leap years?
Yes. EOMONTH knows that February has 29 days in a leap year, so a date in February 2024 returns 29 and a date in February 2023 returns 28.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.