Table of Contents
Sometimes you need to calculate the total number of days in a month to use in the further calculation. It happens specifically when you work on templates and dashboards.
Sometimes you need to calculate the total number of days in a month to use in the further calculation.
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 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 covered within DAY.
- First, when you refer to a date and “0” in EOMONTH it returns the last date of that month. Here the date is on cell A2.
- Second, the DAY function returns the day from the last day returned by EOMONTH.
The day of the month’s last day is always the total number of days in a month. From 31-Mar-2017, it returns 31 which is the total number of days in the March 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 a total number of days from this month.
Formula will be:
=DAY(EOMONTH(DATEVALUE(“01-“&A2&”-“&YEAR(TODAY())),0))
This formula creates a first day’s date of the month you have mentioned and then EOMONTH returns the last date and DAY returns the last day which is the total number of days in a month.
You can use the month’s name both in short form and long-form.
