Date Formatting in Google Sheets
- Go to “Format” > “Number” > Select “Custom date and time“.
- Set the format with AM/PM.
- Now your time will be in a 12-hour format instead of military time.
OR
- Enter a date in text format (e.g., 1-Jan).
- In another cell, write the formula: =TO_DATE(DATEVALUE(cell address)).
- This formula converts text dates to proper date format.
- 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
- Select the cell.
- Go to “Format” > “Number” > “Custom number format“.
- 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 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.
Step 3 (optional)
Go to Date formats and pick your desired date format
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.
Step 1
Go to Format > Number
Step 2
Select custom date and time
Step 3
Set the formatting and Am / Pm
The final output becomes like this in below picture
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))
The above formula will convert any row text date into the proper number date, and this proper date format can be changed.
How to change date format in Google Sheets
Step 1
Select the cell
Step 2
Go to Format > Number > custom date and time
Step 3
Set the format and click Apply
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 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.
Step 1
Go to Format > Number and Click “custom number format”
Step 2
Set the format to dd/mm/yy and you’re done.
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”
Step 2
In the year attribute click the dropdown menu icon and select 30 instead of 1930.
Step 3
Click Apply to save changes
This is how you can change a year in google sheets in a few simple steps
Tutorial: 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