4 Quick Ways to Change the Date Format in Google Sheets (2023)

learn to usespreadsheetit is an endless process. The more we use it, the more shortcuts and efficiency tricks we discover. We recently found a few ways to change the date format in Google Sheets. If you often get confused, this guide will help you understand how Google stores dates and how you can format them.

The easiest way to change the date format in Google Sheets is to change the regional settings. This helps to adapt to the original date format. But you can also manually change the date format and customize everything. You can also automatically convert the date to a different format for a selected column.

Let's get down to business and learn everything we need. First, let's see how changing the region can help.

1. Change the regional settings to use the regional date format

You've been taught to write dates in a certain format, but Google Sheets doesn't support it. But Google Sheets will likely work with settings for a different region.

Therefore, you can change the local region of the spreadsheet. This allows you to use your region's native date format. Look how.

Trio 1:Open the corresponding spreadsheet in Google Sheets and click File.

Trio 2:Select Settings.

Trio 3:Click the Location dropdown menu.

Trio 4:Select the range you want to use in the format.

Trio 5:Finally, click 'Save and Reload'.

And so! The next time you enter a date, it will be accepted in the format used in your local area.

But if you're looking for more controls to change the date format, take a look at the next section.

2. Change the date format manually in Google Sheets

This is the best way to change the date format in Google Sheets while getting full control over the format. You can do what you want regardless of the selected region. Let's see how we can manually customize and change the date format in Google Sheets.

Trio 1:Open the appropriate spreadsheet and click the Format option on the toolbar.

Trio 2:Select Number > 'Custom Date & Time'.

You will see the current date format.

Trio 3:Choose any date format in settings.

Trio 4:Click Apply to save the changes.

But if you want to change individual elements of the date, here's how to do it.

How to change individual date elements

Trio 1:Open the appropriate spreadsheet and click the Format option on the toolbar.

Trio 2:Select Number > 'Custom Date & Time'.

Trio 3:Now click on the item you want to change.

Trio 4:Select the format of the element. This will change the format of that particular element.

While we showed you how to change the months, you can also individually change the days and part of the date by repeating the steps above.

Trio 5:Click Apply to save the changes.

However, if you're still not satisfied and want to change the date order manually, skip to the next section.

How to change the order of dates manually

Trio 1:Open the appropriate spreadsheet and click the Format option on the toolbar.

Trio 2:Select Number > 'Custom Date & Time'.

Trio 3:Click on each of the date items.

Trio 4:Click Delete.

Repeat the process for all three items, making sure the date field is completely blank and removing any symbols like '-' and '*' using the backspace key.

Trio 5:Now open the dropdown menu and select all the items you want on this custom date and in the order you want.

For example: if I want to see the day first, I click on the day, then the month, and then the year. Once this is done, click on each of the elements to change and select a new format as shown in the previous section.

Phase 6:Click Apply to save the changes.

However, if you only want to change the date format for specific cells in your spreadsheet, see the next section.

3. Automatically change the date format in Google Sheets

So you're working on a spreadsheet that already contains a set of dates that conform to the format chosen in Google Sheets. But if you just want to quickly convert them to another format without changing the global date format in Google Sheets, follow the steps below.

Trio 1:Open the corresponding spreadsheet in Google Sheets.

Trio 2:Insert a date range into a Google Sheets column.

Now we want to convert the dates in column A to a format where the month is displayed as text and everything else remains the same. Therefore, we can use the format 'DD-mmm-YYYY'.

How do we assume that 'mmm' means month in text format? There is onedate token guide written by google. You can refer to understand how to enter the date format.

We want these dates to be converted to column B. So we need to enter the following formula into a cell in column B that matches the date in column A.

Trio 2:Enter the formula and press the Enter key. Our dates start in cell A2 of column A and we want to enter this formula in the corresponding cell: B2 in column B.

=arrayfórmula(if((A2:A)<>"",texto(A2:A,"DD mmmm AAAA"),""))

Observation: Modify the formula accordingly, ie. replace 'A2' and 'A' with 'that'where the dates begin and the'column' of the original dates respectively.

Trio 3:Now you can see that all the dates have been converted to a different format.

When you enter a new date in a new cell in column A, it will be automatically converted to "DD-mmm-YYYY" format in column B.

Next, let's see how to convert current dates in text format to a recognizable serial number using the DATEVALUE function.

4. Use DATE VALUE to convert dates to corresponding numeric values

The DATE VALUE function converts a date stored as text into a serial number that Google Sheets recognizes as a date. This is useful when you want to convert dates in text format to a numeric value that will help you with date-related calculations.

All you have to do is enter the formula below in a new cell where you want the converted date value.

=DATA VALUE(data string)

In our case, the text date is present in cell A1 and we want the converted date value in cell B1. We need to enter the formula '=DATEVALUE(A1)' in cell B1. You can also enter the date string directly into the formula.

How to change the date format in Google Sheets. Finally, let's see how we can solve the annoying problem of Google Sheets recognizing text as dates.

Bonus: How to stop Google Sheets from converting numbers to dates

When you enter text in date format, Google Sheets may automatically treat it as a date.

But you only want it as plain text or as a number. Thinking of it as a date also makes the cell incompatible with all formulas and other actions in Google Sheets. However, it is quite simple to restore it to plain text. See how we can do this.

As you can see here, we just entered '11-1', which could be anything. It can be the result of the match or indicate the period from 11:00 to 13:00 in a daily schedule. And as you can see, when I select cell '11-1', Google Sheets displays it in date format.

Trio 1:Select and highlight the cells that are mistakenly considered data.

Trio 2:Click Format > Number and select Plain Text.

Data in all selected cells is not automatically converted to dates.

Here it is! That's all you need to know about how to change the date format in Google Sheets. These methods will definitely come in handy the next time you work in Google Sheets. However, if you have any further questions, please see the FAQ section below.

Frequently asked questions about how to change the date format in Google Sheets

Convert dates easily

Thanks for the predefined date formats provided by Google Sheets. This makes it easy to change date formats in Google Sheets. However, we found that spreadsheets had a steep learning curve. But the increase taken into accountAI ProductsRecently we can expect a more intuitive and easy to use spreadsheet tool!

You can also refer to another useful guide.how to use conditional formatting in google sheets.