How To Change Date Format in Google Sheets
While the concept of writing out a date might seem simple on the surface, there is actually a little more to it.
There are several options for formatting the date, and different areas of the world tend to prefer different styles. When you’re entering data into a spreadsheet using Google Sheets, different formats can change the way the data is presented or viewed. It’s fairly easy to change date formats, though it may look a little confusing in the beginning. We’ll show you how to change date formats in Google sheets and talk about what your options are.
Breaking Down the Date
First things first, let’s talk about the components of writing the date and the general formats that are being used worldwide. Understanding this will help you know what information you need and when.
Elements of the Date
- Number of the day
- Day in the week
- Minutes, Hours, Seconds,
Common Date Formats:
- day/month/year – 20/05/2020
- day-month-year – 20-05-2020
- month/date/year – 05/20/2020
- weekday, month day, year, time (hours:minutes: seconds AM/PM) – Wednesday, May 20, 2020, 08:10:00 AM
Now we have a clear understanding of what can go into a date and different format options. But how can you change it from one to the other in Google Sheets?
Steps to Change the Date Format in Google Sheets
- Open your Google sheet with data in it. Click or select the row(s) or columns(s) or cell(s) which you want to modify
- Select the ‘Format’ option from the menu tab
- Click on ‘Number’ from the drop-down list
- Select Date and the date format will convert into your selected data format
- Also, we can convert the date into many other layouts. Instead of selecting ‘Date’, click on ‘More Formats’ in the drop-down list of ‘Number’
- Now select ‘More date and time formats’
- Next, you should see the following pop up:
As you can see, there are a variety of formats for us to choose from. Observe the box to the left of the ‘Apply’ button. The elements (day, date…) are separated by commas. We can replace those with our choice. For example, we can replace those commas with ‘ – ‘ , ‘ / ‘ .
The magic isn’t over yet – we can still add some more customizations. We can see the boxes in the big box to the left of the ‘Apply’ button, right? Notice that those inner boxes have a symbol – made up of upward and downward arrows
Click on the symbol to see many more options to customize the elements of the date.
Select the required options. Finally, click the format you would like to implement from the pop-up. Click on ‘Apply.’
Now, you have successfully formatted the date in your Google sheet into your desired date format. Let’s take a look at an example of this. Here, we have a sample Google Sheet where the first column has dates in the basic day/month/year format.
Now let’s say that we wanted to change the format of the date in each cell. To do this, you would select each cell you wanted to format and follow these steps:
1. Click Format > Select Number > Select More Formats > Select More Date and Time Formats > I choose the required format I want to apply.
2. In this example, I applied different formats to different cells. If you want to apply a specific format to all the cells in a given a row or a column, click on the row number or column number and the whole column/row will be selected.
Now you can easily switch up your date settings to personalize your spreadsheets in Google Docs!