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.
These are the only up-to-date Excel books currently published and includes the new Dynamic Arrays features.
They are also the only books that will teach you absolutely every Excel skill including Power Pivot, OLAP and DAX.
Some of the things you will learn
