KRS Blog
Excel: Days in Month
November 4, 2011
A simple trick to calculate those pesky dates!
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
|
Month |
2018 |
|
Jan |
31 |
|
Feb |
28 |
|
Mar |
31 |
|
Apr |
30 |
|
May |
31 |
|
Jun |
30 |
|
Jul |
31 |
|
Aug |
31 |
|
Sep |
30 |
|
Oct |
31 |
|
Nov |
30 |
|
Dec |
31 |
|
365 |
Let’s look at 2016 (a Leap Year) to check that February always works…
|
Month |
2016 |
|
Jan |
31 |
|
Feb |
29 |
|
Mar |
31 |
|
Apr |
30 |
|
May |
31 |
|
Jun |
30 |
|
Jul |
31 |
|
Aug |
31 |
|
Sep |
30 |
|
Oct |
31 |
|
Nov |
30 |
|
Dec |
31 |
|
366 |
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.za