When working with dates, sometimes you may need to know the total number of days in a given month.
This could be useful when working on project scheduling or payroll calculation, or even travel planning.
While you can easily figure out the total days in a given month, when doing it for a larger dataset, it would be best to use some simple Excel formulas that would give you the result instantly.
In this short tutorial, I will show you some easy formulas you can use to calculate the total number of days in any given month in Excel.
Get Total Days In a Month Using EOMONTH & DAY 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.
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 that 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 in the cell as the input and then 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 date for the same month.
For example, when the date is 17-Jun-2024, the EOMONTH function would give us 30-Jun-2024 (which is the last day of June 2024).
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.
Note: In case you want to get the total number of days in the previous month or the next month, you can change the second argument of the EOMONTH function (use -1 for the previous month and 1 for the next month)
Also read: How to Get the First Day of the Month in Excel
Get Total Days In a Month Using EOMONTH Function
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,0)-EOMONTH(A2,-1)
Enter this formula in cell B2 and copy it for all the remaining cells in the column.
The above formula uses two EOMONTH functions. The first EOMONTh function uses 0 as the second argument, which gives us the date of the last day of the same month as the date in cell A2.
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.
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 in a column, and I wanted to calculate the total number of days for the month for each date.
Now let me show you a formula that would give you the total number of days in the current month (where the current month value would be automatically picked up from your system settings)
Here is the formula:
=DAY(EOMONTH(TODAY(),0))
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. This formula would dynamically update based on the current date value.
Also read: Calculate Fiscal Year from Date in Excel
Get Total Days When You Have 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.
Below I have the data set where I have the month name in column A, and I want to know how many days are there in each month.
Since the month name is a text string, we will first have to construct a date out of it so that we can get the serial number for the date, and then use that date serial number to calculate the total number of days in that month.
Below is the formula that will do this:
=DAY(EOMONTH(DATEVALUE("01-"&A2&"-"&YEAR(TODAY())),0))
Let me quickly explain how this formula works.
Since we only have the month name, we have used “01-“&A2&”-“&YEAR(TODAY())) 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 “01-January-2023” in cell B2.
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.
Also read: How to Get Month Name from Date in Excel
Total 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 for each date.
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 using the DATEDIF function.
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:
=DATEDIF(A2,EOMONTH(A2,0),"d")
Enter this formula in cell B2 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.
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.
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
