4 Quick Ways to Change Date Format in Google Sheets

4 Quick Ways to Change Date Format in Google Sheets

Learning how to use spreadsheets is an ongoing process. The more we use them, the more shortcuts and efficiency hacks we discover. Recently, we 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 and formats dates.

The easiest way to change the date format in Google Sheets is to change the local region. This allows it to adapt to the native date format. However, you can also manually customize the date format to your liking. Additionally, you can automatically convert dates into a different format for a selected column.

Let’s begin by learning how changing the region can help.

1. Change Locale to Use Regional Date Formatting

You may have been taught a specific date format, but Google Sheets may not accept it. This could be because your Google Sheets settings are different from your region’s settings. To fix this issue, you can change the locale region of your spreadsheet. Here’s how:

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

Step 2: Select Settings.

Step 3: Click on the drop-down menu for Locale.

Step 4: Select the desired region for the date format.

4 Quick Ways to Change Date Format in Google Sheets

Step 5: Click ‘Save and reload.’

4 Quick Ways to Change Date Format in Google Sheets

That’s it! Next time you enter a date, it will be accepted in the local format.

But if you want more controls to change the date format, check out the next section.

2. Manually Change the Date Format on Google Sheets

This is the ultimate way to change the date format on Google Sheets, giving you full control over the format. You can customize it regardless of the selected region. Here’s how you can manually change the date format on Google Sheets.

See also  How to Convert Text to Table (and Vice-Versa) in MS Word

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

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

4 Quick Ways to Change Date Format in Google Sheets

You will see the current format of the date.

4 Quick Ways to Change Date Format in Google Sheets

Step 3: Select a date format.

When dealing with dates, it is essential to choose a format. Select any date format from the options available.

4 Quick Ways to Change Date Format in Google Sheets

Step 4: Click Apply to save the changes.

4 Quick Ways to Change Date Format in Google Sheets

If you want to change the elements of the date, here’s how.

How to Change the Individual Elements of the Date

Step 1: Open the sheet and click on Format in the toolbar.
Step 2: Select Number > ‘Custom date and time’.
Step 3: Click on the element you want to change.

4 Quick Ways to Change Date Format in Google Sheets

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

4 Quick Ways to Change Date Format in Google Sheets

You can change the days and the year part of the date by repeating the above steps.

4 Quick Ways to Change Date Format in Google Sheets

Step 5: Click Apply to save the changes.

4 Quick Ways to Change Date Format in Google Sheets

If you are not satisfied and want to change the date manually, move on to the next section.

How to Change the Date Order Manually

Please follow these steps to format the relevant sheet:

1. Open the sheet and click on Format in the toolbar.

2. Select Number > Custom date and time.

3. Click on each element of the date.

4 Quick Ways to Change Date Format in Google Sheets

Step 4: Click Delete. Repeat this process for all three elements, making sure the date field is completely empty and remove symbols like ‘-‘ and ‘*’ using the backspace key.

4 Quick Ways to Change Date Format in Google Sheets

Step 5: Open the drop-down menu and select the elements you want in the custom date, in your preferred order.

For example: If you want the day first, click on the day, then the month, and the year. Once done, click on each element to change and select a new format.

See also  How to Optimize Bandwidth Usage on Chromebooks

4 Quick Ways to Change Date Format in Google Sheets

Step 6: Apply to save the changes.

4 Quick Ways to Change Date Format in Google Sheets

If you want to change the date format for a few cells in your spreadsheet, check the next section.

3. Automatically Change the Date Format on Google Sheets

If you have a spreadsheet with dates in Google Sheets and want to convert them to a different format without changing the global date format, follow these steps:

1. Open the relevant sheet.

2. Enter a range of dates in a column.

4 Quick Ways to Change Date Format in Google Sheets

To convert the dates in column A so that the month appears as text, use the format ‘DD-mmm-YYYY.’ The ‘mmm’ represents the month. Refer to Google’s date token guide for instructions on how to type the date format. Enter the following formula in a cell in column B to convert the dates:

=ARRAYFORMULA(IF((A2:A)<>“”,TEXT(A2:A,”DD mmmm YYYY”),””))

4 Quick Ways to Change Date Format in Google Sheets

Step 3: All dates are now converted to a different format.

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

4 Quick Ways to Change Date Format in Google Sheets

Next, we will learn how to convert text-format dates into serial numbers using the DATEVALUE function.

4. Use DATEVALUE to Convert Dates to Corresponding Numerical Value

The DATEVALUE function converts a text date to a numerical value recognized as a date by Google Sheets. This is useful for calculating dates. Enter the formula “=DATEVALUE(date_string)” in the desired cell to convert the date. For example, if the date is in cell A1 and the desired cell is B1, enter the formula “=DATEVALUE(A1)” in B1.

4 Quick Ways to Change Date Format in Google Sheets

Here’s how you can change the date format in Google Sheets. Next, let’s find a solution for Google Sheets recognizing text as dates.

Whenever you enter text in the date format, Google Sheets may automatically interpret it as a date. However, if you want the text to remain as plain text or a number, this can be problematic. When a cell is considered a date, it becomes incompatible with formulas and other operations in Google Sheets. Fortunately, converting the text back to plain text is simple. Here’s how:

In the example above, we entered ’11-1,’ which could represent various things such as a game score or a time period from 11 AM to 1 PM. However, when the ’11-1′ cell is selected, Google Sheets displays it in the date format.

See also  5 Best Ways to Fix Mozilla Firefox Spell Checker Not Working in Windows 11

4 Quick Ways to Change Date Format in Google Sheets

Step 1: Select and highlight all cells wrongly considered as a date.

4 Quick Ways to Change Date Format in Google Sheets

Step 2: Click on Format > Number and select Plain text.

Any data in the selected cells will not be automatically converted into dates.

4 Quick Ways to Change Date Format in Google Sheets

This is everything you need to know about changing the date format on Google Sheets. These methods will be helpful the next time you work on Google Sheets. For further questions, please refer to the FAQ section below.

FAQs on Changing Date Format on Google Sheets

1. Changing the date format for one spreadsheet on Google Sheets doesn’t affect all future spreadsheets.

2. To add today’s date in Google Sheets, simply enter the function TODAY() in a cell. You can also refer to a guide for automatic date insertion in Google Sheets.

We appreciate Google Sheets’ preset date formats, which make it easy to change date formats. However, spreadsheets have a high learning curve. With the rise in AI products, we hope for a more intuitive and easy-to-use spreadsheet tool.

You can also check out a helpful guide to using conditional formatting in Google Sheets.

Leave a Comment