Excel: Days in Month

This is the simplest way I know of to find calendar days in a month, using the “zeroth” day of the following month. Formula makes a DAY from supplied inputs:

1) year 2) month + 1 3) zero.

The formula looks like this:

=DAY(DATE(YEAR, MONTH+1,0))

Pretty simple! Another small trick is to use ROW() to get a simple incrementing counter. ROW() returns the current row, and you can add or subtract a base number from it to use as your counter for 1 to 12 (for the month in this example).

The months begin on Row 3, but only 1 is subtracted because we need to arrive at month incremented by one. So, in A3 (Jan), we are finding days in month using day 0 of February.

=DAY(DATE($B$2,ROW()-1,0))

Days in Month

Let’s look at 2016 (a Leap Year) to check that February always works…

Bio: This article was written by Jasper Cook, Excel guru and trombonist of note. Call or email us if KRS can assist you with your Excel: enquiries@krs.co.dev