Date format is one of the most important cell formats in Google Sheets. When Google Sheets recognizes a cell format as a date, it enables the users to make calculations with the date. Google Sheets allows different types of date formats and here you can also customize the date format anytime.
In this article, you’re going to learn how Google Sheets recognizes a date format, how you can change the date format without changing the actual date, make conversions between date and number or text formats, how to extract date from timestamp and more.
What Is Formatting Dates in Google Sheets?
Formatting dates in Google Sheets means how the dates should look after changing their appearance according to your requirements. You can format only the structure of the date but the original date will not change here.
For example, you can show dates as “DD/MM/YY” or “MM/DD/YY” format or even display dates as “1 January, 2024” format. All you can input manually or simply type the first one manually and then formatting others in a range of cells by pasting the first date format only.
Types of Date Format in Google Sheets
In Google Sheets, there are several types of date formats available and you can customize more according to your own requirements. Here are some common date formats in Google Sheets listed below:
- MM/DD/YYYY: The date will be displayed as month/day/year format. For example: 03/24/2024
- DD/MM/YYYY: This format will maintain the structure with date/month/year. For example: 24/03/2024
- Month Day, Year: The month name will be shown here at the beginning. For example: March 24, 2024.
- Day Month, Year: The day will come first here before the month’s name. For example: 24 March, 2024.
- Day-Month-Year: The hyphen will be used here among the day, month name and year. For example: 24-03-2024.
- Day, Month & Day of the Month Combined, Year: This format will include the day of the week too at the beginning. For example: Sunday, March 24, 2024.
How to Change Date Format in Google Sheets
Now we’ll show you how we can alter date format into other date formats based on different global regions or our own customizations.
Changing Date Format in Google Sheets Based on Regions
Google Sheets shows date formats differently based on different locales or regions. When you open Google Sheets app, it detects your locale settings and accordingly shows the default date format for you.
For example, in the United States, the standard date format is usually “MM/DD/YYYY” (e.g., 02/23/2024 for February 23, 2024). But in many European countries, the standard date format is often “DD/MM/YYYY” (e.g., 23/02/2024 for February 23, 2024).
Now if you want to change the locale settings like, changing date format to European, UK or any other region, you have to do the following:
➤ From the menu bar, go to File > Settings.
➤ In the General tab, select any country from the list under the Locale option.
➤ Click “Save and reload” and go back to your spreadsheet.
Now your date format should follow the format according to the locale you have just selected.
Applying Custom Date Format
By applying custom date format in Google Sheets, you can set the parameters for formatting dates according to your specifications. Let’s say, we have a following dataset here:
Now we want to show the dates in Column D as “Weekday, DD-Month Name-YYYY” format. To customize the date format, what we have to do here is:
➤ First select the range of cells where you want to change the date format.
➤ From the menu bar, go to Format > Number > Custom date and time.
➤ Select all the date and time formatting fields one by one and delete them.
➤ Click on the dropdown in the blank field and choose the option Day.
➤ Now click on the Day option and select “Day as full name”.
➤ Next, type a comma after the Date field, and from the drop down choose the option Day again.
➤ Click on the Day option in the field and select “Day with leading zero”.
➤ Type a hyphen (-) after that field.
➤ From the drop down, now select Month.
➤ Click on the Month option in the field and select “Month as full name”.
➤ Put a hyphen again after the Month field.
➤ Expand the drop down and select Year now.
➤ From the Year options, choose “Full numeric year”. The final custom formatting parameters should look like this:
➤ Press Apply and you’re all done.
Now the dates in Column D are formatted with a new customized appearance.
Changing to Default Date Format
If you want to go back to the previous version or the default date format after customization, you have to do the following:
➤ Select the cells with dates.
➤ From the menu bar, go to Format > Number > Dates.
And the dates you’ve selected will go back to the default format (MM/DD/YYYY).
Conversion Between Date, Number and Text Formats in Google Sheets
In this section, we’ll know how to make conversions between date & date serial number and also the conversion between date and text format in Google Sheets.
Conversion Between Date and Number
Converting from Date to Number
Every date has its own date serial number stored in Google Sheets. For example, 03/24/2024 has its own serial number 45375. We can get this number by applying the DATEVALUE function too. But here we’ll directly convert the date to the date serial number by applying the following steps:
➤ Select the dates first.
➤ From the menu bar, go to Format > Number > Number.
And you’ll find all the date serial numbers of the dates.
Converting from Date Serial Number to Date
Now to convert the date serial numbers to dates, what we have to do is:
➤ Select the date serial numbers.
➤ From the menu bar, choose Format > Number > Date.
And the date serial numbers will turn into actual date formats.
Conversion Between Date and Text
Converting from Date to Text
Conversion from date to text format can be often important when you don’t want to make any calculations with the dates or you want to make the date format fixed which will not be anyhow turned into number format later. To turn a date format into text format:
➤ Select the cells with dates.
➤ From the menu bar, choose Format > Number > Plain Text and you’re done.
Converting from Text to Date
To turn date text into date format, you have to do the following:
➤ Select the cells with dates formatted in text.
➤ From the menu bar, choose Format > Number > Date.
How to Convert Timestamp to Date in Google Sheets
A timestamp can contain only time or time and date both. The following dataset has a timestamp column. And we’ll now remove only the times from the column.
To convert the timestamp to date in Google Sheets, follow the steps below:
➤ Select the cells with timestamps.
➤ From the menu bar, choose Format > Number > Date.
And the timestamps will now turn into the date format only as shown below:
Separating Date and Time in Google Sheets
Now we’ll show you how we can separate date and time from the timestamp in Google Sheets. To do this we have to use the SPLIT function. The SPLIT function splits the text into separate parts based on a delimiter. Let’s consider the previous dataset again.
Here, we’ll separate date and time from the timestamps in Column C and show them in different columns D (Date) and E (Time). To do this:
➤ Select cell D2 and type the following formula:
=SPLIT(C2," ")
➤ Press Enter and the timestamp will be split into two parts- Date and Time in two different cells (D2 and E2).
➤ Now copy the formula in other rows in Column D.
A Common Issue with Date Format in Google Sheets and Solution
Sometimes Google Sheets cannot recognize the date format & causes confusions. A common reason here is Google Sheets recognizes date format according to the locale settings.
For example, if your locale settings is set to United States and you type the date as 24/03/2024 (DD/MM/YYYY) then Google Sheets will not consider it a date format and the string will be in text format and left aligned. But if you change your locale settings to UK then the mentioned date format will work properly.
So if a date format doesn’t work properly it’s a better choice to try some different formats unless you want to alter the locale settings.
Frequently Asked Questions
How do I auto write dates in Google Sheets?
To autofill the series of dates in Google Sheets, follow the steps below:
➤ Select a cell and type a date that you want to start with.
➤ Now use the Fill handle tool to drag the cell to the bottom or rightward and the following cells will be filled with sequential dates automatically.
How do I quickly write dates in Google Sheets?
To quickly write today’s date in Google Sheets, use the keyboard shortcuts: Ctrl + ; To include time and date together in a cell, use the keyboard shortcuts: Ctrl + Alt + Shift + ;
Does Google Sheets recognize dates?
Yes, Google Sheets can recognize date formats based on locale settings. If the locale settings is set to the USA region, then Google Sheets will recognize the MM/DD/YY format, not DD/MM/YY. But if the locale settings is changed to the UK region, then it’ll recognize DD/MM/YY date format. Google Sheets has lots of other custom date formats too.
Concluding Words
Date format is important in Google Sheets to make data organized & presentable and do various calculations related to dates. In this article, we’ve covered different aspects of date formatting, including several types of date formats, changing date formats based on regions, applying custom date formats, and converting between date, number, and text formats. Additionally, common issues with date formats were discussed, along with the solutions to remove confusions with date recognition.
By mastering different techniques with date formatting properly, you’ll be able to customize the appearance of the dates to make them more functional and show your expertise a bit more in Google Sheets.