4 Quick Ways to Change Date Format in Google Sheets

Learning how to use spreadsheets is a never-ending process. The more we used it, the more effective shortcuts and tricks we discovered. We recently found several 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 locale. This helps it adapt to the original date format. But you can also change the date format manually and customize everything about it. Furthermore, you can automatically convert the date to a different format for a selected column.

Let’s start and learn everything we need. First, let’s look at how changing regions can help.

1. Change language to use regional date format

So you’ve been taught to write dates in a certain format, but Google Sheets doesn’t accept it. But chances are your Google Sheets is working for a different region’s settings.

Therefore, you can change the native region of the spreadsheet. This will allow it to use a date format that is native to your region. This is the way.

Step 1: Open the relevant sheet in Google Sheets and click File.

Step 2: Select Settings.

Step 3: Click the drop-down menu for Language.

Step 4: Select the area you want to use the format.

Step 5: Finally, click ‘Save and reload’.

That’s it! The next time you enter a date, it will be accepted in the format used in the local area.

But if you’re looking for more controls to change the format of the date, see the next section.

2. Change the date format on Google Sheets manually

This is the ultimate way to change the date format on Google Sheets when you have full control over the format. You can make it the way you want regardless of the selected area. Let’s see how we can customize and change the date format on Google Sheets manually.

Step 1: Open the relevant sheet and click the Format option on the toolbar.

Step 2: Select Number > ‘Custom date and time’.

You will see the current format of the date.

Step 3: Choose any date format from the options.

Step 4: Click Apply to save the changes.

However, if you want to change the individual components of the date, here’s how.

How to change the individual elements of the date

Step 1: Open the relevant sheet and click the Format option on the toolbar.

Step 2: Select Number > ‘Custom date and time’.

Step 3: Now, click on the element that you want to change.

Step 4: Choose the format for the element. This will change the format for just that element.

While we’ve shown how you can change the months, you can also change the day part and the year part of a date separately by repeating the steps above.

Step 5: Click Apply to save the changes.

But if you are still not satisfied and want to change the date order manually, move on to the next section.

How to change the date order manually

Step 1: Open the relevant sheet and click the Format option on the toolbar.

Step 2: Select Number > ‘Custom date and time’.

Step 3: Click on each element of the date.

Step 4: Click Delete.

Repeat the process for all three elements, making sure the date field is completely empty, and remove any symbols like ‘-‘ and ‘*’ with the backspace key.

Step 5: Now open the drop down menu and select all the elements you want in this custom date and in the order you want.

For example: I want to see the previous date, then click on the day, then the month, then the year. Once done, click on each element to change and choose a new format as in the previous section.

Step 6: Click Apply to save the changes.

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

3. Automatically change date format on Google Sheets

So you’re working on a spreadsheet that already contains a set of dates that follow the format selected on Google Sheets. However, if you just want to quickly convert them to another format without changing the general date format on Google Sheets, follow the steps below.

Step 1: Open the relevant sheet in Google Sheets.

Step 2: Enter a date range into a column on Google Sheets.

Now, we want to convert the date in column A to a format where the month appears as text and the rest of the month remains the same. So we can use ‘DD-mmm-YYYY’ format.

How do we assume that ‘mmm’ means month in text format? There is a date token guide written by Google. You can check it to understand how to type date format.

We want these converted dates in column B. Therefore, we need to enter the following formula in a cell of column B that corresponds to the date in column A.

Step 2: Enter the formula and press enter key. Our day starts in cell A2 in column A and we want to enter this formula in the corresponding cell – B2 in column B.

=arrayformula(if((A2:A)<>“”,text(A2:A,”DD mmmm YYYY”),””))

Note: Modify the formula accordingly, that is, replace ‘A2’ and ‘A’ with ‘cell’ where the start date and ‘column‘ of the corresponding original date.

Step 3: Now you can see that all dates are converted to another format.

When you enter a new date in a new cell in column A, it is automatically converted to the ‘DD-mmm-YYYY’ format in column B.

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

4. Use DATEVALUE to convert the date to the corresponding numeric value

The DATEVALUE function converts a date stored as text into a serial number that Google Sheets identifies as a date. This is useful when you want to convert a date into a text format into a numeric value that helps you perform calculations involving dates.

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

=DATEVALUE(date_string)

In our case, the date in the text is 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 a date string directly into a formula.

Here’s 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

Whenever you enter any text in date format, Google Sheets can automatically treat it as a date.

But you just want it in plain text or a number. Furthermore, treating it as a date makes the cell incompatible with all other formulas and operations on Google Sheets. However, restoring it to plain text is pretty straightforward. Here’s how we can do it.

As you can see here, we just entered ’11-1′, which can be anything. It can be the score of a game or represent the period between 11am and 1pm in the daily schedule. And as you can see, when I select cell ’11-1′, Google Sheets shows that cell in date format.

Step 1: Select and highlight all cells that are considered wrong dates.

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

Any data in all selected cells will not be automatically converted to dates.

There you go! That’s all you need to know about how to change the date format on Google Sheets. These methods will definitely come in handy the next time you work on Google Sheets. However, if you have more questions, check out the FAQ section below.

FAQ about Change date format on Google Sheets

1. If I change the date format for a spreadsheet on Google Sheets, will it change the date format for all future spreadsheets?

No, changes in settings will only apply to the specific spreadsheet you’re working on.

2. How do I add today’s date to Google Sheets?

You can enter the TODAY() function into a cell and it will return the current date. You can also check out this tutorial for how to automatically add dates in Google Sheets.

Convert dates easily

We appreciate the preset date formats that Google Sheets offers. Therefore, this makes it easy to change the date format on Google Sheets. However, we feel the spreadsheet has a high learning curve. But with the recent proliferation of AI products, we can expect a more intuitive and easy-to-use spreadsheet tool!

You can also check out another helpful tutorial on using conditional formatting in Google Sheets.

Categories: How to
Source: thpttranhungdao.edu.vn/en/