Essential to Expert Skills progression

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

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

Recent Articles

excel-sequence-function-tutorial

Excel SEQUENCE Function

This tutorial uses a single Excel SEQUENCE function to create a perpetual calendar. Includes sample files and a step-by-step guide.

Calculating loan payment dates

Calculating Loan Payment Dates

This article shows how to calculate loan payment dates in Excel, using a formula that can calculate loans of any length and number of periods.

Leave a Reply

Your email address will not be published.

four × five =