Have you ever needed to extract the date from a timestamp while working in Google Sheets? Luckily, it’s a fairly simple process. In this article, I will show you how to convert a timestamp to a date in Google Sheets.
Understanding Timestamps
When working with timestamps in Google Sheets, you may come across situations where you need to convert them into dates. Perhaps the timestamps were entered using the NOW function or the keyboard shortcut to enter a timestamp (ALT + CTRL + SHIFT + ;). Here’s an example of some timestamps entered in cells:
Timestamps are known as DateTime values, which store both the date and time. However, if you want to use only the date for calculations or simply store the date and remove the time value, you’ll need to learn how to convert them into dates. Let’s dive into the methods for doing so.
Method 1: Formatting the Timestamp
The simplest way to convert your timestamp to a date is by formatting it as a date. This will remove the time portion of the cell, leaving only the date displayed. Here are the steps to follow:
- Select the cells containing the timestamps you want to format as dates.
- In the top menu, click on Format > Number.
- In the drop-down menu, select Date.
- You should now see only the date remaining in the cells you selected in step 1.
Keep in mind that with this method, the time still exists in the cell but is simply not visible. If you need to perform calculations with this value, you may encounter issues. For performing calculations with your timestamps, you’ll need to extract the date from the timestamps, which I’ll show you in the following methods.
Method 2: Extracting the Date Using TO_DATE
If you want to convert your timestamps into dates using a formula, you can use a combination of the TO_DATE and INT functions. The formula to use is:
=TO_DATE(INT(timestamp))
timestamp: The cell that contains your timestamp.
Here are some examples of how this looks in your spreadsheet:
The reason we use the INT function in this formula is that Google Sheets stores date and datetime values as numbers. If we don’t use the INT part of the formula, the time will be stored as a decimal value. INT removes the decimal part, leaving you with just the date.
Method 3: Converting the Timestamp Using SPLIT
Another easy method to convert your timestamps into dates is by using the SPLIT function to split your cell on spaces. This will separate the dates into one cell and the time into another cell. Here’s the syntax to use:
=SPLIT(timestamp, " ")
timestamp: The cell containing your timestamp.
This formula will divide the cell into two cells, separating them by a space. Since the space occurs between the date and time, you’ll get the dates in one cell and the times in another cell. Here’s an example of what this looks like:
In Conclusion
As with everything in Google Sheets, there are multiple methods to remove the time from your timestamps and display only the dates. If you simply want it to visually appear as a date and don’t need to perform any other calculations with the date, formatting it as a date is a quick and easy method.
However, if you actually want to use this date for other purposes, you’ll need to use either method 2 or 3, which store your values as actual dates. We hope you found this tutorial helpful!
For more tips and tricks on Google Sheets and other online tools, visit Crawlan.com.
Note: This article has been adapted and translated from French to English to better suit our audience.
