It can be time-consuming to enter month names in bulk, especially if a spreadsheet already has numerical dates. Luckily, you can simply transpose those numerical dates into names with a few formatting tricks. You could also use a couple of functions. We’ll discuss both ways in this article. Read on to learn more.

How to Convert Month Numbers to Names Using Custom Date Format in Google Sheets

Google Sheets has a pre-defined date format by default. Although you can change which one of them you want to use by following the previous method, you may want to use a custom format that will allow you to show your month name properly.

Let's look at how you can create a custom date format for your spreadsheet. Here are the steps you need to follow:

1. Click and select all the cells you wish to change the date format for.
2. Click on the Format option in the main options bar.
3. In the dropdown menu, click on Number.
4. Click on Custom date and time at the bottom of the dropdown menu. This will open a new window in the middle of your screen.
5. Here, you will have a huge selection of formats you can choose from. If there are already formatting parameters in the box, use backspace to delete them from the text box first.
6. Create your own format by clicking on the text box under the Custom date and time formats title.

   

7. For this example, let's say we want to display the month as a name while the date and the year are displayed as numbers. To do this, click on the Arrow ( ˅ ) icon on the right side of the textbox.
8. We wish to add the month first, so we will click on Month first. You will see it added to the textbox.
9. Click on it and then select the Month as full name option.
10. Click the Arrow ( ˅ ) icon again and then click on Day. Click on the parameter and select the Day without leading zero option.
11. You can add certain characters and symbols in the middle of two parameters, and they will act as a separator. Here, we are simply going to add a space.
12. Finally, we are going to add the year after the day. Click the Arrow ( ˅ ) icon on the Google Sheets dropdown menu again and then click on Year. Click on the parameter and select the Full numeric year option.
13. We will add a Comma ( , ) between the day and year parameters for the separator.

    

14. Click on the green Apply button to save the changes.

If you can't apply the changes, enter any extra parameter in the textbox and press backspace to remove it. You may also be interested in how to calculate the difference between two dates in Google Sheets.

How to Convert Month Numbers to Names Using the TEXT Function in Google Sheets

You can also use the TEXT Function to convert month numbers into names in your spreadsheet. Before we look at an example, let's take a look at the syntax for the formula:

        =TEXT(num, format)
    

The formula requires two parameters. The num parameter defines the time, number, or date to format. The format parameter defines the pattern for the num parameter. Here are the steps you need to follow to use the TEXT parameter in your spreadsheet:

1. Click the cell where you want to use TEXT.
2. Enter the initial part of the formula, which is =TEXT(.
3. Now, we have to write the num parameter. In this case, it is the cell range containing the date, so we will write A1 here.
4. Add a comma symbol to separate the parameters.
5. Now, we will write the format we wish to display the dates. In this case, we want to display the month as a name while the date and the year are displayed as numbers. For this purpose, we will write it as "mmmm d yyyy". Make sure to add quotation marks. You can also add an optional comma in between.
6. Finally, add a bracket to close the formula and press Enter to execute it.

Note: If you want to show a shortened form of the month name instead, you could use mmm instead of mmmm in the formula.

Using the QUERY Function to Change the Date to Text Format in Google Sheets

You can also use the QUERY function in Google Sheets to change numerical dates to text. The QUERY function is capable of plenty of formatting options, but for this example, we'll just focus on the date. Here is an example of a formula that would query a data set in Google Sheets and change the dates in column B to month names:

        =QUERY(A1:C5,"SELECT A, B, C FORMAT B'DD MMMM YY'")
    

Here's a breakdown of how the spreadsheet in the above example was able to change the dates to months using the formula we provided:

1. =QUERY( informed Google Sheets that we are performing a QUERY
2. SELECT and the following sections wrapped in quotes lets the function know we want to show results for columns A, B, and C in the new table it creates.
3. FORMAT B tells the function that we want the formatting for column B to change in the new table.
4. 'DD MMMM YY' tells the function how we want column B to be formatted in the new table. In this case, two-digit numerical days, full month names, and two-digit numerical years.

You can apply a similar formula to your own spreadsheets by simply copying the formula we provided above and changing out the parameters for those that match those in your spreadsheet. For example, if you had dates in column H, you could have FORMAT H instead.

If you want to show your dates in a different format, you could use the following table to help you substitute the correct code into your formula:

Getting Dates Right in Google Sheets

Now that you have a basic understanding of changing number formatting for dates in Google Sheets, you can use those skills to reverse-engineer other formatting changes. Once you are comfortable, you could even start adding validation to the data in your spreadsheets too.