The Smart Method Logo

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

Calendar

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

You can download a sample workbook showing this formula in action.

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.

Download our free book to learn Excel basic skills

At the top of the sidebar you’ll find links to instantly download our free Excel Basic Skills e-books.

Don’t be fooled by the title, some of the skills taught in this 100-page e-book are a mystery even to seasoned Excel professionals. For example, you’ll learn how to model linear and exponential series and how to use multiple-example flash fill to solve many common business problems. Most students can complete it in five hours of less.

And the e-book really is completely and absolutely free (you don’t need to sign up or provide an e-mail – just download it) and it is available for all versions of Excel in common use.

Join the thousands of schools colleges an universities using this completely free resource.

Share this article

Share on facebook
Share on twitter
Share on linkedin
Share on stumbleupon
Share on email

Related Articles

x-lookup-tutorial-part-one

Understand XLOOKUP (part two)

note In most cases, the binary search option is not useful The XLOOKUP function uses a sophisticated linear search method by default. It is my

x-lookup-tutorial-part-one

Understand XLOOKUP (part one)

note XLOOKUP will not work in legacy Excel versions (Excel 2019 and earlier) The XLOOKUP function was added in the Jul 2020 semi-annual version 2002.

Leave a Reply

Your email address will not be published.

four × two =