Date Formatting in Google Sheets [Complete Guide]

How to stop google sheets from auto-correcting dates 25
Key Takeaways: Date Formatting in Google Sheets

Date Formatting in Google Sheets

  1. Go to “Format” > “Number” > Select “Custom date and time“.
  2. Set the format with AM/PM.
  3. Now your time will be in a 12-hour format instead of military time.

OR

  1. Enter a date in text format (e.g., 1-Jan).
  2. In another cell, write the formula: =TO_DATE(DATEVALUE(cell address)).
  3. This formula converts text dates to proper date format.
  4. You can also convert an eight-digit number to date with the formula: =DATE(RIGHT(cell address, 4), MID(cell address, 3, 2), LEFT(cell address, 2)).

OR

  1. Select the cell.
  2. Go to “Format” > “Number” > “Custom number format“.
  3. Set the format to “dd/mm/yy“.

In this article, we will learn about Date formatting in Google Sheets. Also we will learn about how to stop google sheets from auto-correcting dates. Dates are commonly used in google sheets along with many available formats of dates. We will discuss data formats and how to stop google sheets from auto-correcting dates into different formats.

Understanding the Date formatting in Google Sheets

Dates have different formats, there are some commonly used dates formats such as 5-May-2022, 5/5/2022, 5-May-22, etc. We will see how to fix the desired date format and how to stop google sheets to change it automatically. We will also discuss the position of dd-mm-yyyy.

We will see how to adjust the date month and year as per our requirement.

Adding a date is so simple you can directly write in your required format, and if google sheets autocorrect it and show some weird behavior then we will see how to control it.

So first let’s start with the basic dates and dates formats in google sheets.

5-May-2022

If you write the above format of date directly into google sheets then it will accept it and will not do any changes automatically.

This is because we have written the complete date, but many times we don’t have much time to write complete dates so we write quickly, such as 5-may or 5/5/22.

In such formats, google sheets may auto-correct dates because the algorithm behind googles sheets date do not understand the short terms every time so they automatically change them into a nearing complete date format.

We can set out the format and stop google sheets to make any automatic changes.

Today we have different sections regarding dates in google sheets.

We will see all the sections with screenshots and discuss step-by-step implementation in detail.

Note that, a date may be written like a date but it’s interpreted as a number or a string by the google sheets, so for validating your dates you can use a function that returns a Boolean value true or false. If it

returns true then the it’s a date other wise its any other data type.

=ISDATE (cell address)

How to stop google sheets from auto-correcting dates 1

How to Stop Google Sheets From Changing Dates to Numbers

So, in this section we will see how to stop google sheets from changing dates to numbers, oftentimes we have seen that we are aiming to write a date but google sheets change it into a number so there is a very simple solution to this problem.

Step 1

Select the cell or range and Go to Format > Number

Step 2

Select Date.

How to stop google sheets from auto-correcting dates 2

Step 3 (optional)

Go to Date formats and pick your desired date format

How to stop google sheets from auto-correcting dates 3
How to stop google sheets from auto-correcting dates 4

How to stop google sheets from auto-correcting dates 5

This is how we can declare the cell formats and now all the selected cells will behave accordingly and will not lose their formatting automatically.

How to Stop Google Sheets from Changing to Military Time

In this section, we will see how to stop google sheets from changing to military time. Military time is the method of measuring time with full 24 hours measure of the day instead of 12 12 hours of pm and am. Military time is the default time of google sheets, but in this section, we will see how to stop google sheets from changing to military time and keep our time as per 12 hours format.

How to stop google sheets from auto-correcting dates 6

Step 1

Go to Format > Number

Step 2

Select custom date and time

How to stop google sheets from auto-correcting dates 7

Step 3

Set the formatting and Am / Pm

How to stop google sheets from auto-correcting dates 8

How to stop google sheets from auto-correcting dates 9

How to stop google sheets from auto-correcting dates 10

How to stop google sheets from auto-correcting dates 11

The final output becomes like this in below picture

How to stop google sheets from auto-correcting dates 12

This is how we can adjust time formatting and stop google sheets from changing to military time. This is simple, we use many time and date formats as per the requirement/preference

How to Convert Text to Date in Google Sheets

Oftentimes we need to have some calculations done on our dates, but we may face some unknown error while doing it, why is it? It is because we have our dates written like dates but they are text or numbers according to google sheets. google-sheets algorithms interpret them as text and that’s why we get weird results.

You can convert text date into number date by using the below step-by-step procedure.

Step 1

Write a date in text such as 1Jan, 1-Jan, 1jan, or 1 January

Step 2

In other cells write the formula

=TO_DATE(DATEVALUE(cell address))

How to stop google sheets from auto-correcting dates 13

The above formula will convert any row text date into the proper number date, and this proper date format can be changed.

How to stop google sheets from auto-correcting dates 14

How to change date format in Google Sheets

Step 1

Select the cell

Step 2

Go to Format > Number > custom date and time

How to stop google sheets from auto-correcting dates 15

Step 3

Set the format and click Apply

How to stop google sheets from auto-correcting dates 16

How to stop google sheets from auto-correcting dates 17

How to stop google sheets from auto-correcting dates 18

This is how you change the row text date into the proper number date. The formula can also be used for ranges.

We can also convert an eight-digit number into a date by using a formula

The formula for changing an eight-digit number into a proper date is

=DATE (RIGHT (cell address, value), MID (cell address 2 values), LEFT (cell address,2 value))

  • Right () will convert the year
  • Mid () will convert the month
  • Left () will convert the date

See the example below to understand it more accurately

How to stop google sheets from auto-correcting dates 19

How to Change Date Format in Google Sheets to dd/mm/yyyy

In this section, we will see How to change the date format in google sheets to dd/mm/yyyy. We may have different date formats in which we could have mm/dd/yyyy but the most standard format used is dd/mm/yy means day/month/year. We can change any format into this dd/mm/yyyy by following the few steps below.

How to stop google sheets from auto-correcting dates 20

Step 1

Go to Format > Number and Click “custom number format”

How to stop google sheets from auto-correcting dates 21

Step 2

Set the format to dd/mm/yy and you’re done.

How to stop google sheets from auto-correcting dates 22

How to stop google sheets from auto-correcting dates 23

How to stop google sheets from auto-correcting dates 24

This is how we can change any date format into dd/mm/yy.

How to Change the Year in Google Sheets

In this section we will see how to change the year in google sheets, year is a part of the date format, changing only the year is possible in google sheets as well as changing only the date and month. We can use the year attribute inside the custom date format to change it.

A year is normally formatted as 2022, we can also use it as 22 only. So, we will see how to change the year from 2022 to 22. It’s pretty simple

Simply follow the below steps to change the year in google sheets

Step 1

Go to Format > Number > Go to “custom date format”

How to stop google sheets from auto-correcting dates 25

Step 2

In the year attribute click the dropdown menu icon and select 30 instead of 1930.

How to stop google sheets from auto-correcting dates 26

How to stop google sheets from auto-correcting dates 27

Step 3

Click Apply to save changes

How to stop google sheets from auto-correcting dates 28

This is how you can change a year in google sheets in a few simple steps

How to stop google sheets from auto-correcting dates 29

To recap, we learned how to stop google sheets from auto-correcting dates. We have discussed various things in different sections in which we learned the date formatting, we learned the format changing tricks, and we saw a formula that converts raw text date into proper number date. We also discussed a useful method to identify if a written date is formatted as a date or a string or a number.  We also saw how to change the year format only and complete the date-changing format. In beginning, we talked about how to stop google sheets to change time into the military format. We discussed what is military format and how to stop google sheets to convert time into its default format. We saw AM, and PM, and learned how to use them for 12-hours’ time format in google sheets. We saw how to stop auto-correction and how to declare cells to a specific date format. 

Tutorial: Date Formatting in Google Sheets

date formatting in google sheets

Important Notes

  • Remember that when converting text to proper dates, we need to have a raw date written, otherwise, the formula will return an error
  • We can convert 8 digits into date using the formula =DATE(RIGHT(A2,4), MID(A2,3,2), LEFT(A2,2))
  • We can use multiple date formats for multiple dates in our document
  • We can use the =isDate(cell address) function to check if it’s a date or not
  • We can use leading zero or non-zero for dates such as 05-05-2022 or 5-5-2022
  • We can change the year format from 2022 to 22
  • We can also change the time format from 12:10 PM to 12:10 P, and similarly for AM to A
  • We can use a long date format, that shows the name of the day with the full date – 5-5-2022 as Thursday, May 5, 2022
  • We still have many more formatting options for time and date in google sheets, explore them.

Frequently Asked Questions

Can You Use Conditional Formatting for Date Formatting in Google Sheets?

Yes, conditional formatting in google sheets can be used for date formatting. With this feature, you can highlight dates based on conditions you set, such as highlighting past due dates in red or upcoming events in green. This helps visually organize data and analyze date-related information more efficiently.

Conclusion

In this article, we learned how to stop google sheets from auto-correcting dates, we discussed various methods regarding data formats and date conversion in google sheets. This article is for beginner and intermediate users of google sheets, it is designed with a step-by-step procedure with screenshots for your ease.

If you still have any problems you can comment below your problem statement and you will be answered as soon as we can get it. After this article you will be able to answer the following questions; how to stop google sheets from auto-correcting dates? how can we change date formats? how can we use the 12-hour time format in google sheets? how to stop google sheets from changing to military time? what is the is_date function? how can we convert an eight-digit number into a date? How can we use multiple date formats for multiple dates in google sheet document? How can we convert a row text date into a proper number date? and how can we further change the date format of the converted date? All these and many more questions are answered in the above article.

I hope you enjoyed this article and have learned many new things about dates in google sheets. Please share this article with your friends and don’t forget to subscribe to the Office Demy blog for regular updates of further releases about google sheets and much more. Thank you

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq, I am a highly skilled technical writer working full-time for Office Demy. I am specialized in Google Workspace and Microsoft Office applications. With a background in Software Engineering, I possess a deep understanding of the intricate functionalities and features of these productivity tools. Connect me on Linkedin https://www.linkedin.com/in/shaiq-ansari/

OfficeDemy.com
Logo
Enable registration in settings - general