How to Change Month Numbers to Names in Google Sheets

Entering month names in bulk can be time-consuming, especially when a table already contains numeric data. Luckily, you can easily turn that numeric data into names with a few formatting tricks. You could also use a few functions. We will discuss both possibilities in this article. Read on to find out more.

How to convert month numbers to names with custom date format in Google Sheets

Google Sheets has a predefined date format by default. Although you can change which of them you want to use by following the previous method, you might want to use a custom format that allows you to display your month name correctly.

Let’s see how to create a custom date format for your spreadsheet. Here are the steps you need to follow:

1. Click and select all cells for which you want to change the date format.
2. Click on that format Option in the main options bar.
3. Click in the drop-down menu number.
4. Click on Custom date and time below in the drop-down menu. This will open a new window in the middle of your screen.
5. Here you have a huge range of formats to choose from. If there are already formatting parameters in the field, first delete them from the text field with the backspace key.
6. Create your own format by clicking on the text box below 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 year are displayed as numbers. To do this, click on the Arrow ) icon on the right side of the text box.
8. We want to add the month first, so let’s click Month First. You will see that it has been added to the text box.
9. Click on it and then select the Month as full name Possibility.
10. press the Arrow ) icon again and then click Day. Click on the parameter and select it Day without a leading zero Possibility.
11. You can add specific characters and symbols in the middle of two parameters that act as separators. Here we just add a space.
12. Finally we will add the year after the day. press the Arrow ) icon in the Google Sheets drop-down menu again and then click Year. Click on the parameter and select it Full numeric year Possibility.
13. We will add one Comma ( , ) between the day and year parameters for the delimiter.

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

If you can’t apply the changes, enter any additional parameter in the text box 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 to 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. That number The parameter defines the time, number or date to be formatted. That 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 on the cell you want to use TEXT.
2. Enter the beginning part of the formula, which is =TEXT(.
3. Now we have to write them number Parameter. In this case, it’s the range of cells that contains the date, so we write A1 here.
4. Add a comma symbol to separate the parameters.
5. Now let’s write the format in which we want to display the data. In this case, we want to show the month as the name while showing the date and year as numbers. For this purpose we write it as “mmmm d yyyy”. Be sure to add quotation marks. You can also add an optional comma between them.
6. Finally, add a parenthesis to close the formula and press Enter to execute it.

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

Using QUERY function to change date to text format in Google Sheets

You can also use the QUERY function in Google Sheets to convert numeric data to text. The QUERY function offers many formatting options, but for this example we’ll just focus on the date. Here’s an example of a formula that would query a data set in Google Sheets and change the data 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 table in the example above was able to change the dates to months using the formula we provided:

1. =QUERY( Google Sheets informed that we are performing a QUERY
2. CHOOSE and the following quoted sections tell the function that we want to display results for columns AWAY, and C in the newly created table.
3. FORMAT B tells the function that we want the formatting for the column B to change in the new table.
4. ‘DD MMMM YY’ tells the function how to format column B in the new table. In this case, two-digit numeric days, full month names, and two-digit numeric years.

You can apply a similar formula to your own spreadsheets simply by copying the formula provided above and replacing the parameters with those that match those in your spreadsheet. For example, if you had dates in column H, you could do this FORMAT H instead of this.

If you want to display your data in a different format, you can use the table below to help you put the right code in your formula:

Getting dates right in Google Sheets

Now that you have a basic understanding of how to change number formatting for dates in Google Sheets, you can use these skills to reverse engineer other formatting changes. Once you’re comfortable, you can even start validating the data in your spreadsheets.