Separate Date and Time from a Timestamp in Google Sheets

Published: February 10, 2024 - 4 min read

Julian Alvarado

When working with timestamps in Google Sheets, it’s common to need separate date and time components for various analytical tasks. Google Sheets allows you to achieve this efficiently, even if you’re not a spreadsheet expert.

In this step-by-step guide, we’ll show you how to use built-in functions and custom formulas to split date and time from timestamps, enhancing data organization and readability.

Understanding Timestamps in Google Sheets

Google Sheets users frequently encounter timestamps, which are composite datetime values representing both date and time. This section will specifically address the foundational elements of timestamps within Google Sheets, along with methods for referencing real-time data.

The Basics of Timestamps

A timestamp in Google Sheets is a datetime value that contains both date and time information in a single cell. These values are convenient for tracking the exact moment an event occurs and are often automatically generated through various functions or manual input.

  1. Format: The typical format for a timestamp looks something like MM/DD/YYYY hh:mm:ss, where the first part represents the date, followed by the time.
  2. Conversion and Extraction: Users can manipulate these values to extract either the date or time using built-in date and time functions.

Referencing Current Date and Time

To insert the current date and time into a cell, Google Sheets offers several options:

  • =NOW(): This function provides the exact current datetime.
  • =TODAY(): Unlike NOW(), this function only returns the current date and not the time.

These functions are volatile; they update their value every time there are changes made in the sheet or upon refresh. They play an essential role in formulas that require up-to-date information.

Techniques for Splitting Timestamps

When working with timestamps in Google Sheets, it’s essential to know how to separate date and time components effectively. 

There are several methods to accomplish this, from using built-in functions to crafting custom formulas that cater to specific needs.

Using Built-in Functions

Google Sheets provides users with a range of built-in functions to split timestamps into date or time values. The SPLIT function allows one to cut a timestamp at the space character, which typically separates the date and time in a DateTime value. T

his method requires specifying the delimiter, a character or series of characters that indicates where to divide the string.

=SPLIT(A1, ” “)

In the example above, A1 contains the timestamp and a space ” ” is used as the delimiter. This will generate two distinct outputs: one for the date and another for the time, placing them into separate columns.

Custom Formulas for Date and Time Extraction

For users needing a more tailored approach, custom formulas using functions like DATEVALUE and TIMEVALUE are available. These functions perform the extraction by converting the date and time parts of timestamps into numbers representing their equivalent dates and times in Google Sheets.

The DATEVALUE function extracts just the date portion and is apt for converting datetime to date without the time.

=DATEVALUE(TEXT(A1, “mm/dd/yyyy”))

On the other hand, to extract the time component, the formula may include a calculation that subtracts the date-only value from the full timestamp, often resulting in a fraction that represents the time.

spreadsheet ai
Free AI-Powered Tools Right Within Your Spreadsheet

Supercharge your spreadsheets with GPT-powered AI tools for building formulas, charts, pivots, SQL and more. Simple prompts for automatic generation.

=MOD(A1, 1)

Here, MOD is used with 1 as the divisor to yield only the time-related fraction of the timestamp.

Formatting Extracted Values

After extraction, presenting these values in a human-readable format is crucial. Google Sheets lets users format these values as dates or times through the Format menu or by employing custom format functions within formulas.

To configure the display of the date component into day, month, and year, the following format codes can be applied:

=TEXT(DATEVALUE(A1), “mm/dd/yyyy”)

For the time component, to show hour, minute, and second, the appropriate format may look like this:

=TEXT(MOD(A1, 1), “hh:mm:ss”)

These methods facilitate a range of calculations and queries while formatting ensures clarity in the distinction between dates and times within a dataset.

Conclusion 

These techniques empower you to perform various calculations and queries while maintaining clarity in distinguishing between dates and times within your dataset. 

Ready to enhance your Google Sheets skills further? Explore Coefficient for seamless data management and advanced spreadsheet solutions. Get started with Coefficient.

Sync Live Data into Your Spreadsheet

Connect Google Sheets or Excel to your business systems, import your data, and set it on a refresh schedule.

Try the Spreadsheet Automation Tool Over 500,000 Professionals are Raving About

Tired of spending endless hours manually pushing and pulling data into Google Sheets? Say goodbye to repetitive tasks and hello to efficiency with Coefficient, the leading spreadsheet automation tool trusted by over 350,000 professionals worldwide.

Sync data from your CRM, database, ads platforms, and more into Google Sheets in just a few clicks. Set it on a refresh schedule. And, use AI to write formulas and SQL, or build charts and pivots.

Julian Alvarado Content Marketing
Julian is a dynamic B2B marketer with 8+ years of experience creating full-funnel marketing journeys, leveraging an analytical background in biological sciences to examine customer needs.
500,000+ happy users
Wait, there's more!
Connect any system to Google Sheets in just seconds.
Get Started Free

Trusted By Over 50,000 Companies