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.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

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.