How To Stop Google Sheets From Changing Dates

In this tutorial, you will learn how to stop google sheets from changing dates.

Google Sheets has a lot of handy features aimed at saving the user time. Often these can be beneficial, and when they’re not they frequently go unnoticed. Sometimes, however, they can be extremely frustrating, all the more so because it’s not immediately obvious why Google Sheets is changing the values users enter into the spreadsheet.

One common place this can happen is when entering dates. There are a large number of different ways to format dates and, depending on your location and purposes, you’re likely to have different ideas about which one you want to use. For example, you might format the date January seventeenth 2022 as 2022-01-17, 1/17/22, 17.01.22, Jan 17, etc. However, often Google Sheets will change the way the date is displayed after you type it in. If this happens to you, don’t worry, you can easily correct the issue and get the date format you want by following the steps below

Changing Number Formatting

Google changes the dates you enter to match the cell’s number formatting, so the fix is to correct the cell’s number formatting to reflect the date format you want to use. Here’s how:

Step 1

Select the cells you want to change the date format of and open the Format menu, hover over Number and choose from the available date number formats

Step 2

If the date format you want is unavailable, you can choose the Custom Date Format option at the bottom of the Number Format menu to create a custom date format

Step 3

In the Custom Date and Time Formats window, choose date and time elements like year, day, and month from the dropdown to the left of the Apply button

Step 4

Type special characters such as slashes, dashes etc between the date elements to format the date, and hit Apply when you’re done

Step 5

The selected cells will be formatted according to the date format you entered

Clearing Formatting & Auto Detection

The above method always works, but it can be time consuming, especially if for whatever reason your spreadsheet requires a lot of different date formats. Fortunately, there’s a quick fix that allows you to type dates in any format and have Google Sheets not change them. The downside to this is that, if you type the date wrong, Google Sheets will pick the wrong date format and stick to, so you’ll have to repeat this process

Step 1

Select the cells you want to enter dates in and open the Number Format menu again, this time choose Automatic

Step 2

Now, when you type dates in those cells, Google Sheets will detect the date format from the text you type. Once a date format has been detected for that cell, all future dates entered in that cell in any format will take that date format

Summary

Example Spreadsheet: Make a copy of the example spreadsheet

In this tutorial, I covered how to stop google sheets from changing dates. Want more? Check out all the Google Sheets Tutorials.