How to Get the Number of Days in a Month in Excel?

If you want to know how many days a month has, you can easily figure it out for one date. But doing it by hand for a whole column of dates gets slow.

February makes it trickier, since it has 29 days in a leap year. But nothing to worry about, as a simple formula can give you the result instantly.

In this tutorial, I’ll show you some easy formulas to get the number of days in any month, plus the days left and the working days in a month.

Get Days in a Month Using DAY and EOMONTH Functions

The easiest way to get the total number of days in a month is by using a combination of the DAY and EOMONTH functions.

Knowing the days in a month could be useful when working on project scheduling or payroll calculation, or even travel planning.

Let me first show you how it works, and then I’ll explain the formula.

Below I have a data set where I have some dates in column A, and I want to find out the number of days in the month to which each date belongs.

Here is the formula that will give me the total number of days in each month:

=DAY(EOMONTH(A2,0))

Enter this formula in cell B2 and copy it for all the remaining cells in the column.

Now let me quickly explain how this formula works.

The EOMONTH function (where EO stands for End Of) takes the date as the input and gives the last day of that month.

Note that the second argument in the EOMONTH function needs to be 0, as we need the last day of the same month.

For example, when the date is 17-Jun-2025, the EOMONTH function would give us 30-Jun-2025 (which is the last day of June 2025).

The DAY function then gives us the day value of the last date of the month, which would essentially be the total number of days in the month.

Since this uses the actual last day of the month, it handles leap years automatically. February 2024 gives 29, and February 2026 gives 28.

Pro Tip: To get the total days in the previous month or the next month, change the second argument of EOMONTH. Use -1 for the previous month and 1 for the next month.

If you’re using Microsoft 365 or Excel 2021 and later, you may want one formula that fills the whole column instead of copying it down.

But if you use =DAY(EOMONTH(A2:A11,0)), you’ll get a #VALUE! error. This is because EOMONTH doesn’t spill when you give it a range.

The fix is to add a plus sign before the range:

=DAY(EOMONTH(+A2:A11,0))

Enter this formula in cell C2, and it spills down automatically to give you the result for all the dates.

The plus sign turns the range into a list of values, which EOMONTH is happy to work with. I’ve covered this in detail in why some Excel functions won’t spill.

I’ll use this plus sign in the rest of the EOMONTH formulas in this article, so a single formula gives you the result for all the dates.

Also read: How to Get the First Day of the Month in Excel

Get Days in a Month Using the DATE Function

Here’s another way to get the days in a month, and it’s useful when you have the year and the month number in separate columns instead of a full date.

Below I have a data set where I have the year in column A and the month number in column B, and I want the number of days in each month.

Here is the formula to do this:

=DAY(DATE(A2:A11,B2:B11+1,0))

Enter this formula in cell C2, and it spills down automatically.

The DATE function builds a date from a year, month, and day. Here, I’ve given it the next month (B2:B11+1) and 0 as the day.

Day 0 of a month is Excel’s way of saying the day before the 1st, which is the last day of the previous month.

So DATE(2026,2,0) gives us 31-Jan-2026, and the DAY function then returns 31.

This also works for December. DATE(2025,13,0) rolls over to 31-Dec-2025, so you don’t need to handle it separately.

It’s the same day 0 trick you can use to get the last day of the year in Excel.

Since DATE works with ranges directly, this formula spills without the plus sign. If you have full dates instead, you can use =DAY(DATE(YEAR(A2:A11),MONTH(A2:A11)+1,0)).

In older versions of Excel, use =DAY(DATE(A2,B2+1,0)) in cell C2 and copy it down.

Get Total Days in a Month Using Two EOMONTH Functions

Another fast way to get the total number of days in a month is by using two EOMONTH functions with the subtraction operator.

In this case, we calculate the last day of the month for the given month and the previous month, and then simply subtract the two values.

This works as date values are stored as numeric values in the back end.

Let me demonstrate how it works by using an example.

Below I have a data set where I have the dates in column A, and I want to get the total number of days in each month.

Here is the formula to do this:

=EOMONTH(+A2:A11,0)-EOMONTH(+A2:A11,-1)

Enter this formula in cell B2, and it spills down for all the dates.

The above formula uses two EOMONTH functions. The first one uses 0 as the second argument, which gives us the last day of the same month as the date in column A.

And the second EOMONTH function uses -1 as the second argument, which gives us the date of the last day of the previous month.

And since the dates are stored as serial numbers in the back end, when we subtract these two EOMONTH function results, we get the total number of days in the month.

In older versions of Excel, use =EOMONTH(A2,0)-EOMONTH(A2,-1) in cell B2 and copy it down.

Also read: How to Convert Serial Numbers to Dates in Excel

Get Total Days in the Current Month

In the above examples, I had a set of dates (or years and months) in columns, and I wanted to calculate the total number of days in the month for each row.

Now let me show you a formula that would give you the total number of days in the current month (where the current date is picked up automatically from your system).

Here is the formula:

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

In the example file, I’ve also put =TODAY() in cell A2, so you can see which month the formula is using.

This formula uses the TODAY function to get the current date, and then the EOMONTH function is used to get the last date of the current month.

The result of the EOMONTH function is then used by the DAY function to get the total number of days in the current month.

Note that the TODAY function is volatile and would automatically recalculate to show the current date. So this formula updates on its own when the month changes.

Also read: Calculate Fiscal Year from Date in Excel

Get Total Days When You Have the Month Name Only

In this section, I will show you how to calculate the total number of days in the month when you only have the month’s name.

Since February can have 28 or 29 days, you also need the year to get the right answer.

Below I have a data set where I have the month name in column A and the year in column B, and I want to know how many days there are in each month.

Since the month name is a text string, we will first have to construct a date out of it, and then use that date to calculate the total number of days in that month.

Below is the formula that will do this:

=DAY(EOMONTH(DATEVALUE("1 "&A2:A11&" "&B2:B11),0))

Enter this formula in cell C2, and it spills down automatically.

Let me quickly explain how this formula works.

Since we only have the month name, we have used “1 “&A2:A11&” “&B2:B11 to first construct a date in a format that Excel recognizes as a proper date.

In our example, this part of the formula would give us “1 January 2026” for the first row.

Since this is still a text string, I have used the DATEVALUE function to get the corresponding serial number for this date.

Once I have the serial number, I used EOMONTH to get the last day of the month and then the DAY function to get the total number of days in that month.

You don’t need the plus sign here, because DATEVALUE already hands EOMONTH a list of dates instead of a range. This also works with short month names such as Jan or Feb.

In older versions of Excel, use =DAY(EOMONTH(DATEVALUE("1 "&A2&" "&B2),0)) in cell C2 and copy it down.

If you don’t have a year column and just want the current year, replace B2:B11 (or B2 in the older version) with YEAR(TODAY()).

Just note that February will then show 28 or 29 depending on the year you open the file.

Important: DATEVALUE reads the text based on your computer’s date and language settings. With non-English settings, English month names may give a #VALUE! error.

If you need the month number instead of the day count, see how to convert a month name to a number in Excel.

Also read: How to Get Month Name from Date in Excel

Get the Number of Days Left in the Month

In all the examples above, I have shown you how to get the total number of days in the month.

But what if you want to know the total number of days left in the month after the given date? This can easily be done by subtracting the date from the last day of its month.

Below I have a data set where I have some dates in column A, and I want to know the total number of remaining days in that month.

Here is the formula that will do this:

=EOMONTH(+A2:A11,0)-A2:A11

Enter this formula in cell B2, and it spills down for all the dates.

EOMONTH gives us the last day of the month, and subtracting the date from it gives the number of days remaining.

Important: Excel may show this result as a date (such as 1/17/1900) instead of a number. If that happens, select the results and change the format to General from the Home tab.

Note that this counts the days after the given date. So for 30-Apr-2026, the result is 0. If you want to count the given date as well, add 1 to the formula.

You can also get the same result using the DATEDIF function:

=DATEDIF(A2,EOMONTH(A2,0),"d")

Enter this formula in cell C2 and copy it for all the remaining cells in the column.

DATEDIF function takes three arguments:

• the start date – which is the date in column A
• the end date – which we have calculated using the EOMONTH function so that it is the last day of the month
• “d” – this is the code that tells the function to give us the total number of remaining days between the start and the end date

DATEDIF returns a plain number, so you won’t need to change the format here.

Get the Number of Working Days in a Month

If you only want to count the working days (Monday to Friday) in each month, you can use the NETWORKDAYS function along with EOMONTH.

Below I have a data set where I have some dates in column A, and I want to know the number of working days in the month of each date.

Here is the formula that will do this:

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

Enter this formula in cell B2, and it spills down for all the dates.

EOMONTH(+A2:A11,-1)+1 gives us the first day of the month (the last day of the previous month, plus one day). EOMONTH(+A2:A11,0) gives us the last day of the month.

NETWORKDAYS then counts the days from Monday to Friday between these two dates, including both of them.

This formula only skips weekends and doesn’t know about your holidays. To exclude holidays too, you can add a holiday list as the third argument of the NETWORKDAYS function.

If your weekend isn’t Saturday and Sunday, use the NETWORKDAYS.INTL function instead.

In older versions of Excel, use =NETWORKDAYS(EOMONTH(A2,-1)+1,EOMONTH(A2,0)) in cell B2 and copy it down.

So these are some of the formula methods you can use to quickly calculate the total number of days in any given month in Excel.

I hope you found this Excel tutorial useful.

Other Excel articles you may also like:

• How to Calculate the Number of Days Between Two Dates in Excel
• Calculate the Number of Months Between Two Dates in Excel
• How to Remove Time from Date/Timestamp in Excel
• Calculate Number of Weeks Between Two Dates in Excel
• How to Add or Subtract Days to a Date in Excel
• Get Day Name from Date in Excel
• How to Autofill Only Weekday Dates in Excel (Formula)
• Convert Days to Months in Excel
• Business Days Calculator
• WORKDAY Function in Excel