How to Separate Date and Time from a Timestamp in Google Sheets

Have you ever found yourself in a situation where you only need the date or the time from a timestamp in Google Sheets? In this article, we will explore three methods to quickly and efficiently separate the date and time from a timestamp in your Google Sheets.

Method 1: Using the SPLIT Function

The SPLIT function allows you to split the content of a cell into multiple cells. Since a timestamp consists of both the date and time, we can utilize the SPLIT function to extract the date into one cell and the time into another. Here’s how:

1. In cell B2, enter =SPLIT(A2, " "). This will automatically write the date in cell B2 and the time in cell C2. Don’t forget to include the space between the quotation marks, otherwise, you will encounter an error.
2. Copy this formula to the other cells in column B.

Method 2: Using the INT Function

In the internal system of Google Sheets, the date is stored as an integer, while the time is stored as a decimal or fractional part. To extract the date and time from the timestamp, we can use the INT function to round down to the nearest lower integer. Follow these steps to obtain the date:

1. In cell B2, enter =INT(A2).
2. If the format is not already in date format, follow these steps:
   • Click on cell B2.
   • Go to Format, click on Number, and then select Date.
   • Click on cell B2 again.
   • Go to More Formats, and click on Date.
3. Copy the formula in cell B2 to the other cells in column B.

To obtain the time:

1. In cell C2, enter =A2-B2.
2. If the format is not already in time format, follow these steps:
   • Click on cell C2.
   • Go to Format, click on Number, and then select Time.
   • Click on cell C2 again.
   • Go to More Formats, and click on Time.
3. Copy the formula in cell C2 to the other cells in column C.

Method 3: Changing Cell Format to Date or Time

If you prefer a simpler approach, you can easily separate the date and time by changing the format of the cells. Here’s how:

1. Copy the timestamp values to columns B and C.
2. To obtain the date, click on column B. You can then choose one of the following:
   • Go to Format, click on Number, and then select Date.
   • Go to More Formats, and click on Date.
3. To obtain the time, click on column C. You can also choose one of the following:
   • Go to Format, click on Number, and then select Time.
   • Go to More Formats, and click on Time.

By using any of these three methods, you can easily separate the date and time in your Google Sheets. Your table will appear as shown below:

If you wish to further customize the format of the date and time, select the cells you want to modify, click on the “123” icon in the spreadsheet toolbar, click on “More Formats,” and then select “More date and time formats.”

Supercharge Your Spreadsheets with Crawlan.com

To take your Google Sheets skills to the next level and discover more tips and tricks, visit Crawlan.com. At Crawlan, you’ll find valuable resources and advice to enhance your Google Sheets experience and boost your productivity!

Remember, separating the date and time from a timestamp in Google Sheets doesn’t have to be complicated. With these methods and the right tools, you can quickly organize and analyze your data effectively. Happy spreadsheet-ing!