This article shows you how to find number of days in a month to make your month-driven calculations dynamic. DAY and EOMONTH functions help us for this tip.

Syntax

=DAY(EOMONTH(Date, 0))

Steps

  1. Start with =DAY(
  2. Continue with EOMONTH(
  3. Select or type the range that includes date value B3,
  4. Type 0 as EOMONTH’s 2nd parameter
  5. Type )) to close functions and press Enter to complete the formula

How

Our goal here is to find the last day of a month, instead of finding the number of days in a month from the first day to the last. Excel already has the EOMONTH function which returns the date of the last day of the month of specified date. However; the EOMONTH function has one more argument than the date itself which can be used to calculate for the previous or the next month. To ensure the EOMONTH works on the date we want, we need to set the second argument to 0.

EOMONTH(B3,0)

Next step is to find the number associated with the last day of the month that is returned by EOMONTH. The DAY function gets the job done here. It simply returns the day value from a date. Obviously, the last day of the month equals to the number of the days in the month.

=DAY(EOMONTH(B3,0))

Also see other date related articles how to count years between two dates with DATEDIF, and how to count months between two dates with DATEDIF.