Convert Month Name to Days in Google Sheets

We can utilize the EOMONTH, MONTH, DATE, and DAY functions in Google Sheets to convert a month’s name into the number of days in that month.

The role of each function will be as follows:

  • MONTH: Converts month text to month number.
  • DATE: Creates the month’s start date.
  • EOMONTH: Converts the date to the end of the month date.
  • DAY: Retrieves the day part from the end of the month date.

The following formula in Google Sheets will convert the month name “January” to 31, which represents the number of days in January 2024:

=DAY(EOMONTH(DATE(2024, MONTH("January"&1), 1), 0))

If you specify the month name in cell A1, you can replace “January” with A1 in the formula.

Examples of Converting Month Names to Days

In the following example, assume we have a month name in cell A1.

Enter the following formula in cell B1 to convert the month name in cell A1 to the number of days in that month:

=DAY(EOMONTH(DATE(2024, MONTH(A1&1), 1), 0))
Conversion of month name to days in Google Sheets

Formula Breakdown:

DATE(2024, MONTH(A1&1), 1)

This follows the syntax: DATE(year, month, day).

  • We can obtain the end of the month date using the EOMONTH function in Google Sheets.
EOMONTH(DATE(2024, MONTH(A1&1), 1), 0)

This follows the syntax: EOMONTH(start_date, months), where start_date is the date returned by the DATE function, and months is 0, representing zero months to advance.

The DAY function returns the day part from the month’s end date, corresponding to the number of days in that month.

Note:

The formula uses the year 2024. Thus, when you convert a month name to days in that month, it will consider the year 2024. You can replace the year with any specific year. If you want to obtain the number of days in a month concerning the current year, feel free to replace 2024 with YEAR(TODAY()).

Generate a List of Month Names and the Number of Days in Each Month

In an earlier tutorial, we saw how to generate a list of month names in Google Sheets: How to Get Sequence of Months in Google Sheets

The following formula entered in cell A1 will return the month names from January to December in cells A1:A12:

=ArrayFormula(TEXT(EDATE(DATE(YEAR(TODAY()), 1, 1), SEQUENCE(12, 1, 0)), "MMMM"))

To get the number of days in each month in this list, you can use our earlier formula in cell B1 and drag the fill handle down or use the following array formula:

=ArrayFormula(DAY(EOMONTH(DATE(2024, MONTH(A1:A12&1), 1), 0)))
Month list and the number of days in each month

Feel free to replace 2024 with the year you want or use YEAR(TODAY()) to specify the current year dynamically.

Resources

Here are some related resources.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.