Find Number of Days in a Month in Google Sheets (An Easy Guide)

Finding the number of days in a month in Google Sheets is a matter of simply understanding how the application perceives dates and using a combination of functions that can achieve our goals.

Let’s get started.

Table of Contents

How to Calculate the Number of Days in a Month in Google Sheets

Calculating the number of days in a month is really a matter of simple understanding of calendar months.

At first glance, things may seem complicated as we know that the day number of each month fluctuates. But, if you take a step back, you’ll see that it is a simple matter of extracting the last day number of a month. For example, the last date of January is 31, thus the total number of days for the month of January is 31.

So, what we essentially have to do is find the last day of the given month. Which can be easily done with the EOMONTH function.

EOMONTH(start_date, months)

And…

Extract that day number from the EOMONTH result to find the number of days in the given month. This can be done by the DAY function.

DAY(date)

Step 1: Calculating the last date with EOMONTH:

=EOMONTH(B3,0)

We used a cell reference for the “start_date”. In most practical scenarios, the date will usually be given in a column, so a cell reference is the best way to go.

As for the “months” field, we have input 0, as we want to keep our calculation in the given month.

Step 2: We insert the EOMONTH formula within the DAY function to extract the number of days in the month in Google Sheets.

=DAY(EOMONTH(B3,0))

And that’s it!

Google Sheets already has a built-in API of a calendar to help its users. We take advantage of that fact and use it in our date calculations.

Calculating With Different Date Formats

Date formats are versatile. It can depend on region, workplace, or even personal preference. As long as a date format is valid, Google Sheets will recognize it and thus can be used in calculations.

One of these calculations is our topic of the day: counting the number of days in a month in Google Sheets.

Let’s see a few examples of this calculation for different date formats:

1. The date is in the YYYY-MM format:

Note that the date in the Date column is a text. Google Sheets is intelligent enough to recognize a date format regardless of whether it is a string or a number. But if you want to convert a text to a date, please have a look at our How to Convert Text to Date in Google Sheets article.

2. The date has partial month name instead of a month number:

3. Mixed date formats:

Notice that, no matter what date format the worksheet presents, our formula can easily grasp the underlying value and successfully calculate the total number of days in that month.

Further Examples and Iterations of the Calculation

1. Number of Days in the Current Month in Google Sheets

To calculate the number of days in the current month, you will need to find or generate the current date to use in the formula. The easiest way to generate the current date is to use the TODAY function.

The TODAY function doesn’t require any arguments to generate the current date. However, it gives us a “running” date, meaning that it updates the date every time the worksheet updates.

It’s not much to think about, unless you plan on using multiple of them in the same worksheet. Then, it might make your processes slow down.

Formula to calculate the number of days in the current month:

=DAY(EOMONTH(TODAY(),0))

=DAY(EOMONTH($C$2,0))

Remember we talked about how too many TODAY functions can slow down your worksheets processes?

Well, the easiest remedy for that is to have a single cell containing the TODAY function while the formulas that use the function use a cell reference to retrieve that value.

For us, we have used a locked cell reference (a cell reference with absolutes ($)) to do this. The cell reference is $C$2.

2. Number of Days in the Previous or Next Month in Google Sheets

Another useful calculation we can perform is to find the number of days of a month from a given date.

The only change that will occur in our formula is in the “months” field of the EOMONTH function.

To jog our memory: the “months” field determines how many months from the “start_date” that the EOMONTH function will calculate the last day of the month from.

Calculate the Number of Days for Months After

For months after the start date, the “months” value will be positive. Thus, to find the number of days of just one month after the start date, the formula will be:

=DAY(EOMONTH(B3,1))

=DAY(EOMONTH(B3,C3))

We can use cell reference to automatically set the number of months. In the image, our starting month is January. The next month (1 month after) will be February. Thus, the results show the total number of days in the month of February.

A few more results:

Calculate the Number of Days for Months Previous

Understandably, the “months” field of the formula will now have a negative value to determine the number of months previous to the start date.

The formula:

=DAY(EOMONTH(B3,-1))

=DAY(EOMONTH(B3,C3))

As you can see, the formula that uses cell reference is the same for both cases, making it the best way to approach this problem.

From Current Date

Since the current date is involved, we once again will use the TODAY function in our formula.

=DAY(EOMONTH(TODAY(),months))

Or better yet, using cell references:

=DAY(EOMONTH($C$2,B5))

Since we have only a single cell containing the current date, we have used an absolute cell reference for it. We have left the month cell reference unlocked to help us automatically fill the column with the fill handle.

Final Words

Finding the number of days in a month in a Google Sheets worksheet has been made that much easier thanks to intuitive but simple functions like EOMONTH and DAY. This goes to show that almost any calculation may be possible in the application with the right combination of functions.

Feel free to leave any queries or advice you might have for us in the comments section below. Or have a look at some of our other date-related articles that are sure to interest you.