If you are working with dates in Excel, you may need to calculate the total number of days in a specific month. Excel has a function that can help you do this easily. In this article, we will show you how to use the MONTH and YEAR functions in combination with the EOMONTH function to get the total number of days in a month.
Guide How to Get Total Days in Month in Excel
Start by selecting the cell where you want to display the total number of days.
Type the following formula:
=EOMONTH(A1,0)-DATE(YEAR(A1),MONTH(A1),1)+1In the formula, replace “A1” with the cell that contains the date for which you want to calculate the total number of days.
Press Enter, and the total number of days in the month will be displayed.
Explanation of the Formula
- EOMONTH(A1,0) returns the last day of the month of the date in cell A1. The 0 argument tells Excel to use the current month (i.e. the 0th month from the current date).
- DATE(YEAR(A1),MONTH(A1),1) returns the first day of the month of the date in cell A1. This is necessary because we want to find the number of days between the first and last days of the month.
- Subtracting the two values gives us the total number of days in the month.
- The final “+1” in the formula adds one day to the total, because we want to include the first day of the month in the count.
Things to Keep in Mind
- Make sure the cell containing the date is formatted as a date.
- Ensure that the date format in the formula matches the date format in the cell.
- The formula assumes that the date is in cell A1, so adjust the formula accordingly if the date is in a different cell.
- The formula only works for dates in the Gregorian calendar, which is the calendar used in most countries today.
How to Get Total Days in Month in Excel Example
Let’s say we have a date in cell A1 that represents the month we want to find the total number of days for. To use the formula, we would enter it in another cell (let’s use cell B1). Now, the reference cell A1 as the argument for the “EOMONTH” and “DATE” functions
Let’s say we have the date “15/04/2023″ in cell A1.
We can enter the formula in cell B1 like this:
=EOMONTH(A1,0)-DATE(YEAR(A1),MONTH(A1),1)+1
The formula will return the value 30. As we can see above which represents the total number of days in April 2023.