A quick way to return the number of days in any given month using a formula.
Contents:
Formula
= EOMONTH(date,0) - EOMONTH(date,-1)Simply replace the "date" with any input date, either hard coded value or a cell reference, to return the number of days in the month in which that date is found.
Explanation
In this example, we are combining two EOMONTHs, one to return the end the previous month and the other to return the end of the current month.
The 0 in the first EOMONTH is telling the function to return the end of the month that the date occurs in, while the -1 in the second EOMONTH gives the end of the previous month.
All is left is to subtract the two EOMONTHs from each other to return the number of days in any month.
It may be easier to see what the formula is doing if we look at each part side by side:
In the second column, we are calculating the end date of the previous month, and in the third column, the end date of the current month.
The fourth column is simply combining both formulas, and subtracting them to leave the correct numbers of days in each date/month.
Note:
This formula does take leap years into account.