 Publishers of the world’s most comprehensive and up-to-date Excel tutorials ## Excel tutorials

The only Excel books you will ever need

• The only constantly updated Excel 365 titles more…
• Thoroughly covers Power Pivot and Power Query more…
• Covers business intelligence and OLAP features more…
• Simple, easy, understandable, 2-page lessons more…
• Available as both printed books and e-Books more…
• Used by schools, colleges and universities  more…
• Available for 365, 2019, 2016 and Mac versions more…

# Excel formula to get number of days in a month

Sometimes you may need to extract the number of days in the month that a given date falls into, most likely when working with due dates or expiry dates.

This can be done using Excel’s DATE function, and can be done most easily by making use of some ‘secret’ knowledge of how the function works.

• If you’re unfamiliar with Excel formulas and functions you could benefit greatly from our free Basic Skills e-book (see sidebar).
• You can also find a much more in-depth look at the DATE function in our Expert Skills Book/e-book.
• Our Essential Skills and Expert Skills tutorials take students on a journey from absolute beginner to Excel guru in a series of simple two-page lessons.

## The DATE function and day zero

It’s a little-known fact that Excel’s DATE function understands the concept of months having a ‘day zero’. Day zero is always the last day of the previous month.

Knowing this makes it easy to find out the number of days in the month for any date. All you need to do is find ‘day zero’ of the following month.

Here’s the formula, assuming your date is in cell A2:

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

Assuming your date is the 5th of November 2018, this is equivalent to:

=DAY(DATE(2018,11+1,0)

## Sample workbook ## An alternative solution

There’s no need to use the ‘secret’ day zero. You can do the same thing by using the DATE function to get the first day of the following month and subtracting one:

=DAY(DATE(YEAR(A2),MONTH(A2)+1,1)-1)

This has exactly the same result, it’s just a slightly longer formula.