- 1 4 Quick Ways to Change Date Format in Google Sheets
- 1.1 1. Change Locale to Use Regional Date Formatting
- 1.2 2. Manually Change the Date Format on Google Sheets
- 1.3 3. Automatically Change the Date Format on Google Sheets
- 1.4 4. Use DATEVALUE to Convert Dates to Corresponding Numerical Value
- 1.5 FAQs on Changing Date Format on 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.
Step 5: Click ‘Save and reload.’
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.
Step 1: Open the relevant sheet and click Format in the toolbar.
Step 2: Select Number > ‘Custom date and time’.
You will see the current format of the date.
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.
Step 4: Click Apply to save the changes.
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.
Step 4: Select the format for the element. This will change the format for just that element.
You can change the days and the year part of the date by repeating the above steps.
Step 5: Click Apply to save the changes.
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.
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.
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.
Step 6: Apply to save the changes.
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.
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”),””))
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.
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.
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.
Step 1: Select and highlight all cells wrongly considered as a date.
Step 2: Click on Format > Number and select Plain text.
Any data in the selected cells will not be automatically converted into dates.
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.
As Fred Humiston, the author behind Twothirds.us, I focus on providing practical and easy-to-follow guides and solutions for a variety of technical issues that people commonly face.
My articles cover a wide range of topics, from troubleshooting out-of-sync audio on TikTok and managing Firestick devices, to understanding the impact of Capcut on video quality and resolving sound issues on Hisense Roku TVs.
I also delve into more general tech tips, like how to block numbers on landlines or use VPNs without Wi-Fi. My goal is to make technology more accessible and less intimidating for everyone, whether you’re a tech novice or a seasoned user. I strive to offer clear, concise, and helpful advice to make your digital experience smoother and more enjoyable.