Date formatting in Google Sheets is essential for efficiently managing dates and organizing datasets. Google Sheets provides a versatile array of date formatting options that allow you to tailor your data presentation to your needs. By simply navigating to the ‘Format’ tab in the main menu and selecting the ‘Number’ option, you can easily transform cells into different date formats.
Dates play a pivotal role in data analysis and report generation, enabling you to sort, filter, and calculate values within specific date ranges seamlessly. However, it’s important to note that Google Sheets demands adherence to specific date input formats.
In this comprehensive guide, we’ll walk you through the process of adjusting date formats in Google Sheets. Furthermore, we’ll explore valid date formatting options, including customizing the default date format to suit various countries and much more.
Don’t miss the chance to gain a complete understanding of date formatting in Google Sheets – read on until the very end.
Download the Example Google Sheet
Before we proceed further in the article, make sure to download the Google Sheet containing the spreadsheets and datasets we will use to demonstrate the date formatting.
With this sheet, you can follow me along and practice the things we will discuss.
If you have your own Google Sheet ready with the date data, then skip downloading the above file.
How to Change Date Format in Google Sheets
Every date in spreadsheet programs like Google Sheets is treated as a number.
Consider the following example:
The numbers in cells C2 and C3 respectively represent both of the aforementioned dates. These numbers are crucial for calculations in Google Sheets.
A date can be written in various formats. As human beings, we may be able to interpret it quickly. However, in the case of Google Sheets, it supports a limited number of date formats.
You need to make sure to write the date in a predefined format for Google Sheets to understand.
Task: Let us convert “1/1/2023” to “January 1, 2023” in Google Sheets.
Here are the steps:
- Open a new Google Sheet by clicking here
- Type “1/1/2023” in the cell “A2”
- Hover over the main menu and click on the “Format” tab
- Select the “Number” option from the popup
- Choose “Custom date and time”
- A new dialog box will open. You will see a range of predefined date formats in Google Sheets
- Scroll down and select “August 5, 1930” from the list
- Click on the “Apply” button in the green
Google Sheets will instantly convert the date from “1/1/2023” to “January 1, 2023” as shown in the above GIF.
You can use the same process to convert an integer value to a date.
Google Sheets has a huge number of date formats predefined within the program. If you are not happy with them, then please refer to the next section of the article.
Date formatting in Google Sheets: Setting Custom Date Formats
In case you wish to represent a date in a specific format, here is the solution for you.
Google Sheets allows you to create a custom date format.
Task – Change the date format from “1/1/2023” to “1 January 2023” in Google Sheets
- Select the date
- Hover to the main menu and click on “Format” tab
- Select the “Number” option from the popup
- Choose “Custom date and time”
- A new dialog box will appear, which is divided into two sections: List of Date Formats and Editor. Refer to the following image.
- Click at the end in the Editor box, as shown below
- Press the “Delete” key until you erase everything
- Next, click on the Dropdown icon at the end of the Editor box
- You will see a list of options. Select the “Day” from the Date section
- Click on the “Day (5)” button to see various formatting options available
- Chose the first option, “Day without leading zero (5)”
- Click in the Editor box
- Press the “Space” key on your keyboard
- Click on the Dropdown icon
- Select “Month”
- Click on the “Month (8)” button to see various formatting options available for the month
- Choose the fifth option, “Month as full name (August)”
- Click on the Editor box
- Press the “Space” key
- Click on the Dropdown icon
- Select “Year”
- Click on the “Year (30)” button from the Editor box
- Choose “Full numeric year (1930)” from the list
- Press the “Apply” button in the green
You can use the above steps to format multiple dates written in any number of formats. Please select all of them and follow the steps above to standardize the date formatting for all.
How to change the default date format to the United Kingdom
Our Google Sheets is set up to display the date in the United States date format, which is month, day, and year (mm-dd-yyyy).
You can change it to your country to display dates in a format specific to your country.
Here are the steps to change the default date format to the United Kingdom,
- Open a Google Sheet
- Hover to the main menu and click on the “File” button
- Select “Settings” from the popup
- A new dialog box will appear, as shown below
- Click on the dropdown below the option “Locale”
- Choose “United Kingdom” from the list
- Click on the “Save and reload” button in the green
Once you are done, the Google Sheets will reload the current tab and all your dates will be displayed as day, month, and year (dd-mm-yyyy), which is the United Kingdom date format.
How to convert a date to timestamp (date and time) in Google Sheets
Timestamps are nothing but date and time in one cell.
Suppose you record the event entries at a specific date and time. The Google Sheets comes with the DateValue feature, where you enter the date and time in a single cell.
Task – Convert the date “1/30/2023” to timestamp (date and time) “1/30/2023 10:00:00” in Google Sheets
Here are the steps,
- Open Google Sheet
- Select the cell containing the date
- Hover to the main menu and click on “Format” tab
- Choose the “Number” option from the popup
- Now, select “Date and time” as shown below
- The date is converted to the timestamp. Double-click on the timestamp
- Use your keyboard to move the cursor to the time section of the timestamp
- Edit it as “10:00:00”
- Press “Enter” key
Note that while editing the timestamp, avoid picking up the date from the calendar popup, as it will make your timestamp disappear.
How to validate a date format in Google Sheets
Note that all of the above steps to change the date format are applicable to valid dates in Google Sheets.
Let’s consider the following example,
In the above image, not all of the dates are valid and acceptable by Google Sheets.
There are three ways to check if the date is valid or not.
METHOD #1 – Check the Alignment
Dates that are entered correctly and follow the predefined format are shifted to the right of the cells. Refer to the following image.
In the above image, the first three dates are valid, and the remaining two are invalid.
Sidenote: If you are unable to see the alignment of the content in the spreadsheet cells, make sure to increase the width of the cells/columns.
METHOD #2 – Use the ISDATE function
The ISDATE function in Google Sheets displays TRUE for valid dates and FALSE for invalid dates.
It is a simple formula with the following general syntax,
=ISDATE(value)The “value” argument needs to be replaced with the date you wish to validate. Note that instead of manually entering the date, you can also provide the cell reference value.
After using the ISDATE function, the output will be as follows,
In the above image, the ISDATE function has returned TRUE for the first three dates and FALSE for the rest of the two dates.
METHOD #3 – Use the DATEVALUE function
The DATEVALUE function is mainly used to convert a valid date to an integer number. So, if the function returns an error, then it means the date is not valid.
It has the following syntax,
=DATEVALUE(date_string)The “date_string” argument needs to be replaced with the date you wish to convert to a number or validate. You can manually enter the date or provide the cell reference.
Here are the results after using the DATEVALUE function,
The formula has generated integer numbers for the first three dates and returned the “#VALUE!” error for the remaining dates, as shown above.
Date formatting in Google Sheets (Conclusion)
There are various methods to change the date format in Google Sheets. Apart from the above methods and functions, you can use various functions and QUERY to extract and convert numbers and text to date.
We tried to keep it simple with the most common scenarios and solutions to them.
If you are still stuck somewhere or having any particular issue, then feel free to comment below. Our team is always happy to assist you.
Moreover, you can explore our blog section, which is full of useful tips and tricks to manage dates in Google Sheets.