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.

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)

eomonth function syntax - days in month in google sheets

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)

day function syntax in google sheets

Step 1: Calculating the last date with EOMONTH:

=EOMONTH(B3,0)

using eomonth function to find the last day of the month

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))

using eomonth and day function to find the number of days in a month in google sheets

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:

day number of a month from yyyy-mm date 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:

using partial month name as date format

3. Mixed date formats:

find the number of days in a month from 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.

today function syntax

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))

Or

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

finding the number of days in the current month in google sheets

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))

Or

=DAY(EOMONTH(B3,C3))

breakdown for calculating the number of days of a month after the start date

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:

calculating the days in a month after the start date in google sheets

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))

Or

=DAY(EOMONTH(B3,C3))

calculating the days in a month before the start date in google sheets

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))

calculating the number of days in a month from the current date in google sheets

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.

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for officewheel.com specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo