Find the Number of Days in a Month with Excel Formula (2024)

How many days are there in February 2023?

Which months of the year have 30 or 31 days? 📆

Are you someone who always checks the calendar to find the number of days in a month? 👀

Well, Excel can definitely help you out.

In this lesson, we’ll teach you how to find the number of days in a month in Excel 😊

Calculate days in a month in Excel

You can calculate the number of days in a given month from just a date 😀

To do that, you need to use a formula based on the EOMONTH and DAY functions in Excel.

Specifically, this formula:

=DAY(EOMONTH(date,0))

As you see, this formula is a combination of two functions.

We have the EOMONTH function within the DAY function.

Let’s break them down one by one 👇

  1. The EOMONTH function returns the last date of the given month.

=EOMONTH(start_date,months)

01 EOMONTH functions have start date as first argument and months as second argument
  1. The DAY function returns the day as an integer (number of days) given a date serial number.

=DAY(serial_number)

02 DAY returns date into total number of days

But you don’t have to use the EOMONTH function and then the DAY function one by one. Let’s skip the two-part process 😉

You can calculate the number of days in a month from just a date by using this formula:

=DAY(EOMONTH(date,0))

Let’s do it.

  1. Double-click a cell and type the formula:

=DAY(EOMONTH(

03 calculate number of days in a month from date in previous month
  1. Type the date or click the cell reference where the date is entered.

=DAY(EOMONTH(B3,

04 calculate number of days from a current date
  1. Type “0” for the months and close the formula with 2 right parentheses.

=DAY(EOMONTH(B3,0))

05 determine number of days
  1. Press Enter.

Fill in the rest of the rows by dragging down or double-clicking the fill handle.

06 total number of days in the month

Hooray! 🥳

We’ve got the total days in a month already from just a date.

How the formula works: 💡

Well, the EOMONTH first returns the last day of the month we specified.

Then the DAY returns the number of days in a month from that end date.

In our example, the formula determined that there are 31 total days in the month of January from a given date of 1/23/2023.

Pro Tip:

If you press enter and you see a date format instead of the number of days as a result, there’s no need to worry 😊

You just need to change the Number Format for that cell.

To do that:

  1. Go to the Home Tab.
  2. In the Number group, click the dropdown arrow to select a Number format.
  3. Select General.

That’s it – Now what?

There you have it! You can totally let Excel do the counting for you 😀

Now, you can calculate how many days in the month of January, February in a leap year or not, and in any month.

Excel formulas can definitely change the way you work with your data.

You only need to learn what functions to use and how to use them so you can work faster and easier than ever âš¡

And I got you covered for that.

Join my Beginner Excel training that lets you get started with Excel in the right way.

This training is FREE, simple, and specifically adapts to your skill level so you don’t get overwhelmed 😊

Other resources

We have compiled all the date and time functions for you in our All Excel’s Date and Time Functions Explained! article.

Here, you can comprehensively learn how to add dates or times, find the difference between two dates or times, and so much more 😀

Also, a neat trick is to use EOMONTH to find the first day of a month – here’s how!