Calculating the total number of days in a month in Excel is essential for tasks involving time, scheduling, or budgeting.
For example, we might want to compute the total days in a month in Excel for the followings reasons:
- Project planning: If we are planning a project, we need to know the total number of days in a month to schedule and allocate resources accurately.
- Payroll calculations: If we are calculating the number of days an employee has worked in a month, we need to know the total number of days in that month to ensure accurate calculations.
- Budgeting: If we create a budget or financial plan, we want to know the total number of days in a month to accurately allocate our expenses and income.
This tutorial shows some easy formulas you can use to get the total days in a month in Excel.
Method #1: DAY and EOMONTH Function to Get the Total Days in a Month
The EOMONTH function calculates the serial number representing the last day of the month after or before a specified number of months, and the DAY function calculates the day of the month, a number from 1 to 31.
We can use a formula combining the DAY and EOMONTH functions to get the total number of days in a month from a given date.
Let’s consider the following dataset showing particular dates in each month of 2023.
We want to apply a formula combining the DAY and EOMONTH functions to determine the total number of days in the months of 2023 from the dates shown in column A.
We use the following steps:
- Select cell B2 and enter the below formula.
=DAY(EOMONTH(A2,0))
- Drag the Fill Handle or double-click the Fill Handle to copy down the formula.
The total number of days for each month of 2023 is displayed in column B.
The EOMONTH function returns the serial number representing the date of the month’s final day for the date in cell A2.
The second argument, 0, specifies that we want to determine the last day of the same month as the date provided.
The DAY function returns the date value for the serial number returned by the EOMONTH function.
In this case, the value returned by the DAY function is equal to the number of days in the month since its argument is the last day of the month.
Also read: Calculate Days Between Two Dates in Excel (Workdays/Weekends)
Method #2: Get Total Days in a Month Using EOMONTH Function
The EOMONTH function returns the serial number of the last day of the month before or after a specified number of months.
We can use a formula with the EOMONTH function to compute the months’ total days from a given date.
Let’s consider the following dataset showing particular dates in each month of 2023.
We calculate the number of days in each month as follows:
- Select cell B2 and enter the below formula:
=EOMONTH(A2,0)-EOMONTH(A2,-1)
- Double-click the Fill handle or drag the Fill Handle to copy the formula to cells down the column.
The total days in each month of 2023 are shown in column B.
In this formula, we subtract the previous month’s last day from the current month’s last day to ascertain the total number of days in the current month.
Also read: How to Get the First Day Of The Month In Excel?
Method #3: Use a Formula Combining DATEDIF and EOMONTH Functions
The DATEDIF function calculates the days, months, or years between two dates, and the EOMONTH function determines the serial number of the month’s final day before or after a given number of months.
For example, given the first day of a month, we can use a formula combining the two functions to compute the number of days in the month.
Presume we have the following dataset showing the first day of each month of 2023 in column A.
We want to utilize a formula combining the DATEDIF and EOMONTH functions to calculate the total number of days in each month of 2023, given the first day of each month in column A.
We use the below steps:
- Select cell B2 and enter the formula below:
=DATEDIF(A2,EOMONTH(A2,0),"d")+1
- Double-click the Fill Handle or drag the Fill Handle to copy the formula to the other cells in the column.
This formula uses the DATEDIF function to compute the difference between the month’s first and last days.
The “+1” at the end includes the first day of the month.
Note: Excel offers the DATEDIF function to support older workbooks from Lotus 1-2-3. However, as we enter the function, Excel doesn’t display suggestions for its arguments, so we must memorize them.
The syntax for the DATEDIF function is as follows:
=DATEDIF(start_date,end_date,unit)
Also read: How to Convert Days to Years in Excel (Formulas)
Method #4: Getting the Total Number of Days in the Current Month
Let’s say we are working on a dashboard and want to include the following feature that dynamically displays the total number of days in the current month.
We must tie this feature to a dynamic formula using the following steps:
- Select cell A2 and type in the following formula:
=DAY(EOMONTH(TODAY(),0))
- Press Enter.
The total number of days in the current month, February 2023, is shown in cell A2.
The TODAY function returns the current date, and the EOMONTH function returns the serial number representing the final date of the month of the current date.
Finally, the DAY function returns the date value for the serial number returned by the EOMONTH function.
This formula is dynamic in that it automatically returns the total number of days of a month each time the month changes.
Also read: Calculate Days Left in the Year (Formula)
Method #5: Get Total Days in a Month Using Month Name and Year Value
Presume we have the dataset below showing the months of 2023.
We want to apply a formula combining the DAY, EOMONTH, and DATEVALUE functions to determine the total days in each month of 2023.
Here are the steps:
- Select cell C2 and enter the below formula:
=DAY(EOMONTH(DATEVALUE("01-"&A2&"-"&B2),0))
- Drag the Fill Handle or double-click the Fill Handle to copy the formula to the other column cells.
The total number of days in each month of 2023 is displayed in column C.
This formula creates the first day of each month of 2023:
- The DATEVALUE function converts the date argument passed as a text string to a number representing the date.
- The EOMONTH function returns the serial number representing the date of the month’s final day for the date passed to it by the DATEVALUE function.
- The DAY function returns the date value for the serial number returned by the EOMONTH function, equivalent to the number of days in the month, because the argument is the last day of the month.
Also read: Find Last Monday of the Month Date in Excel (Formula)
Method #6: Get Total Days in a Month Using Month Name
Let’s say we have the following dataset showing the months of the current year.
We want to enter a formula in column B to calculate the total number of days in each month of the current year.
We use the following steps:
- Select cell B2 and enter the formula below:
=DAY(EOMONTH(DATEVALUE("01-"&A2&"-"&YEAR(TODAY())),0))
- Drag and double-click the Fill Handle to copy the formula down the column.
The total days in each month of the current year are shown in column B.
This formula creates the first day of a particular month, and the EOMONTH function returns the serial number representing the final date of the month.
Finally, the DAY function uses the value returned by the EOMONTH function to return the last day of the month, equivalent to the total days in the month.
Also read: How to Add Days to a Date in Excel
Method #7: Use a Formula Combining DAY, DATE, YEAR, and MONTH
Suppose we have the following dataset showing specific dates in each month of 2023.
We calculate the number of days in each month as follows:
- Select cell B2 and enter the below formula:
=DAY(DATE(YEAR(A2),MONTH(A2)+1,1)-1)
- Double-click the Fill handle or drag the Fill Handle to copy the formula to cells down the column.
The total days of each month are displayed in column B.
In this formula, the DATE function constructs a date for the final day of the month of the date in cell A2.
This construction is done by extracting the year of the date in cell A2 using the YEAR function, adding 1 to the month of the date, and setting the day of the date to 1, giving us the first day of the following month.
The formula subtracts 1 (one) from this value to get the month’s last day. Finally, the DAY function extracts the day value from the last day of the current month.
This tutorial showed eight techniques for gettings the total days in a month in Excel. We hope you found the tutorial helpful.
Other Excel articles you may also like:
- How to Highlight Dates Before Today in Excel?
- How to Convert Date to Day of Week in Excel?
- Count Days from a Date to Today in Excel (Formula)
- How To Highlight Weekends In Excel?
- Convert Number of Days to Weeks in Excel
- Convert Days to Months in Excel
- How to Convert Text to Date in Excel?
- How to Apply Long Date Format in Excel?
- How to Calculate the Number of Months Between Two Dates in Excel?
