Summary

To get the number of days in a given month from a date, you can use a formula based on the EOMONTH and DAY functions.

In the example shown, the formula in cell B5 is:

=DAY(EOMONTH(B5,0))

Generic formula

=DAY(EOMONTH(date,0))

Explanation 

The DAY function returns the day component of a date. The EOMONTH function returns the last day of the month for a given date. So, in this formula EOMONTH first returns a date corresponding to the last day of the month, and then DAY returns the date value for that date.

By definition, the value returned by DAY is always equal to the number of days in the month, since the date supplied to DAY is always the last day.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.