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

Formula Breakdown:

• MONTH(A1&1): This part converts the month text to a month number.
• The DATE function uses the month number to return the month start date.

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

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.

• How to Return Nth Occurrence of a Day in a Month in Google Sheets
• Calculate Week Number Within Month (1-5) in Google Sheets
• Convert Month Numbers to Month Names in Google Sheets
• Months and Actual Days Between Two Dates in Google Sheets
• Formula to Sort By Month Name in Google Sheets
• How to Convert Date to Month and Year in Google Sheets
• Find the First and Last Workdays of a Month in Google Sheets
• Get Last Saturday of Any Given Month and Year in Google Sheets
• Finding the First Sunday, Monday, etc., Date in a Month with Google Sheets