Converting Text Months to Actual Dates

Yasmin has a column that contains text month names, such as "January," "February," etc. She would like to convert these to actual date values for the last day in each month, such as 1/31/26, 2/28/26, etc. Yasmin wonders if this can be done with a formula, or if she needs to create a macro.

A macro is not necessary in this case; a formula will do just fine. In fact, there are multiple formulaic approaches that could be used. The easiest, however, is to use the EOMONTH function. For instance, the following will provide the desired result:

=EOMONTH("1 "&A1&" 2026",0)

What the formula does is to construct a string that contains a full date, such as "1 January 2026". The EOMONTH function expects a valid date serial number as the first parameter, but is only too happy to convert a text string to a serial number automatically. The second parameter, 0, indicates that you want the end-of-month date for whatever month is in the first parameter.

Speaking of months in the first parameter, you can make the formula a bit shorter by simply leaving off the day of the month, in this manner:

=EOMONTH(A1&" 2026",0)

To get results for a different year, just change the year references in the formula. If you want the results for months in the current year, then you can stick the day back on and drop the year, in this manner:

=EOMONTH("1 "&A1,0)

The absolute shortest version of using EOMONTH to do the conversion is the following:

=EOMONTH(A1&1,0)

Assuming A1 contains the month "January," then the formula constructs the string "January1" (notice there is no space in there) and uses that as the first parameter for EOMONTH. When you use this short version, the date returned is for the current year.

When you use one of the formulas above, the result is a date serial number. In order to see it as a date, you will need to apply a date format to the cell in which the formula is located.

If, for some reason, you have a version of Excel in which EOMONTH doesn't automatically convert a text date to a date serial number, you can always wrap the first parameter with the DATEVALUE function, in this manner:

=EOMONTH(DATEVALUE("1 "&A1&" 2026"),0)

Finally, be aware that any of these formulas will work just fine if you have a short version of the month name in cell A1. In other words, you could use "Oct" instead of "October" and the result would be exactly as desired.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13995) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.