Convert Timestamp to Date in Google Sheets: Easy Guide

Jason Gong
LAST UPDATED
June 6, 2024
TL;DR

Use DATEVALUE or INT functions to convert timestamps to dates.

By the way, we're Bardeen, we build a free AI Agent for doing repetitive tasks.

Since you're working in Google Sheets, check out our GPT in Spreadsheets feature. It automates data formatting and conversion tasks, saving you time.

Timestamps are a common way to record dates and times in Google Sheets, but they can be difficult to read and analyze in their raw format. Converting timestamps to a more readable date format is essential for effective data management and analysis. In this step-by-step guide, we'll show you how to convert timestamps to dates in Google Sheets using built-in functions and formatting options.

Understanding Timestamps in Google Sheets

A timestamp is a sequence of characters that represents a specific date and time. In Google Sheets, timestamps are often stored as strings in various formats, such as "MM/DD/YYYY HH:MM:SS" or "YYYY-MM-DD HH:MM:SS". Converting these timestamps to a more readable date format is crucial for data analysis and visualization.

Timestamps can be generated automatically by Google Forms or imported from external sources like databases or APIs. By connecting Google Sheets, you can:

  • Sort and filter data by date
  • Create pivot tables and charts based on date ranges
  • Calculate durations between dates
  • Apply date-based formatting and conditional formatting

In the following sections, we'll explore different methods to convert timestamps to dates in Google Sheets, including built-in functions and custom formatting options.

Using DATEVALUE to Convert Timestamps

The DATEVALUE function in Google Sheets is a powerful tool for converting string timestamps to a proper date format. To use DATEVALUE, simply pass the timestamp as a string argument to the function. For example:

  • If your timestamp is in cell A1 and in the format "YYYY-MM-DD HH:MM:SS", you can use the formula =DATEVALUE(A1) to convert it to a date.
  • If your timestamp is in a different format, like "MM/DD/YYYY HH:MM:SS", you may need to first use the SUBSTITUTE function to replace the separators with hyphens: =DATEVALUE(SUBSTITUTE(A1,"/","-")).

Here's a step-by-step example of converting the timestamp "2023-04-15 09:30:00" in cell A1 to a date:

  1. In cell B1, enter the formula =DATEVALUE(A1).
  2. Press Enter to see the result, which will be the date value corresponding to April 15, 2023.
  3. You can then format cell B1 as a date using the Format menu to display it in your preferred date format.

By using DATEVALUE, you can easily extract the date portion of a timestamp and perform date-based calculations or formatting in your Google Sheets. For more advanced uses, check out how to bring AI into your spreadsheet with GPT features.

Save time and focus on important work by learning to add ChatGPT to Google Sheets. Let AI handle your repetitive tasks.

Using DATEVALUE to Convert Timestamps

The DATEVALUE function in Google Sheets is a powerful tool for converting string timestamps to a proper date format. To use DATEVALUE, simply pass the timestamp as a string argument to the function. For example:

  • If your timestamp is in cell A1 and in the format "YYYY-MM-DD HH:MM:SS", you can use the formula =DATEVALUE(A1) to convert it to a date.
  • If your timestamp is in a different format, like "MM/DD/YYYY HH:MM:SS", you may need to first use the SUBSTITUTE function to replace the separators with hyphens: =DATEVALUE(SUBSTITUTE(A1,"/","-")).

Here's a step-by-step example of converting the timestamp "2023-04-15 09:30:00" in cell A1 to a date:

  1. In cell B1, enter the formula =DATEVALUE(A1).
  2. Press Enter to see the result, which will be the date value corresponding to April 15, 2023.
  3. You can then format cell B1 as a date using the Format menu to display it in your preferred date format.

By using DATEVALUE, you can easily extract the date portion of a timestamp and perform date-based calculations or integrate with Excel in your Google Sheets.

Utilizing INT Function for Date Conversion

The INT function in Google Sheets is another useful tool for converting timestamps to dates. Unlike DATEVALUE, which extracts the date portion from a string timestamp, INT truncates the decimal portion of a number, effectively removing the time part of a timestamp.

When you have a timestamp in a numeric format (like 44663.39583), you can use the INT function to convert it to a date by simply passing the cell reference or the numeric value to the function. For example:

  • If your numeric timestamp is in cell A1, you can use the formula =INT(A1) to convert it to a date.
  • If you have the timestamp value directly, you can use the formula =INT(44663.39583) to get the corresponding date.

Here are a few more examples of using INT for timestamp conversion:

  1. Timestamp in A1: 44663.39583 (which represents 2022-04-15 09:30:00)
    =INT(A1) returns 44663, corresponding to 2022-04-15
  2. Timestamp in B1: 44664.75 (which represents 2022-04-16 18:00:00)
    =INT(B1) returns 44664, corresponding to 2022-04-16

Keep in mind that the INT function only works with numeric timestamps. If your timestamps are in a string format, you'll need to use DATEVALUE or other functions to convert them first. Additionally, you can integrate Google Docs with Google Sheets to enhance your workflow.

Save time by using Bardeen's automation guides designed to make your document management more efficient.

Converting Unix Timestamps with EPOCHTODATE

Google Sheets offers a dedicated function, EPOCHTODATE, for converting Unix timestamps to readable dates. Unix timestamps represent the number of seconds or milliseconds that have elapsed since January 1, 1970 (the Unix epoch).

To use the EPOCHTODATE function, follow this syntax:

  • =EPOCHTODATE(timestamp, [unit])

The timestamp argument is the Unix timestamp you want to convert, and the optional unit argument specifies the unit of time for the timestamp:

  1. 1 (default) indicates the timestamp is in seconds
  2. 2 indicates the timestamp is in milliseconds
  3. 3 indicates the timestamp is in microseconds

For example, to convert a Unix timestamp in seconds (e.g., 1655906568) to a date, use:

  • =EPOCHTODATE(1655906568)

If your timestamp is in milliseconds (e.g., 1655906568000), specify the unit argument as 2:

  • =EPOCHTODATE(1655906568000, 2)

Both formulas will return the date: 6/22/2022 14:02:48.

Keep in mind that EPOCHTODATE returns the date and time in UTC (Coordinated Universal Time). To display the result in your local time zone, you can wrap the EPOCHTODATE function inside the ARRAYFORMULA and add the appropriate time zone offset.

Formatting and Customizing Date Outputs

After converting timestamps to dates in Google Sheets, you can customize the format of the output to match your preferences or maintain consistency across your data sets. Google Sheets provides a variety of built-in date formatting options as well as the ability to create custom date formats.

To format dates in Google Sheets:

  1. Select the cells containing the dates you want to format.
  2. Click on the "Format" menu and choose "Number" > "Date."
  3. Choose from the predefined date formats or select "More formats" for additional options.

If the predefined formats don't meet your needs, you can create a custom date format:

  1. Follow steps 1-2 above, but choose "More formats" > "More date and time formats."
  2. In the "Custom date and time format" field, enter your desired format using special codes like "MM" for month, "DD" for day, and "YYYY" for year.
  3. Click "Apply" to apply the custom format to your selected cells.

To ensure consistency in date formats across your data sets, consider:

  • Establishing a standard date format for your organization or project.
  • Using custom date formats consistently across all relevant sheets and files.
  • Documenting your chosen date format and sharing it with collaborators.
  • Periodically auditing your data to identify and correct any inconsistencies in date formatting.

By taking advantage of Google Sheets' date formatting options and maintaining consistency, you can improve the readability and usability of your data for analysis and reporting purposes. For more advanced uses, check out how to add ChatGPT to Google Sheets to automate and enhance your data management.

Automate your data updates and save time. Learn how to enrich LinkedIn profile links in Google Sheets with Bardeen.

Advanced Techniques: Array Formulas and Scripts

When dealing with large datasets containing timestamps, you may need to convert multiple timestamps at once. Array formulas in Google Sheets can help you achieve this efficiently. An array formula is a formula that performs multiple calculations on one or more sets of values, returning either a single result or multiple results.

To use an array formula for bulk timestamp conversions:

  1. Enter your conversion formula in the first cell where you want the converted dates to appear.
  2. Instead of pressing Enter, press Ctrl+Shift+Enter (or Cmd+Shift+Enter on a Mac) to create an array formula.
  3. The formula will automatically apply to the entire column or range, converting all the timestamps at once.

For more complex or conditional date conversions, you can use Google Apps Script to create custom functions. Google Apps Script is a scripting language based on JavaScript that allows you to extend the functionality of Google Sheets.

To create a custom function using Google Apps Script:

  1. In Google Sheets, go to "Tools" > "Script editor."
  2. Write your custom function using JavaScript, defining the input parameters and the desired output.
  3. Save the script and give it a meaningful name.
  4. Back in your Google Sheet, you can now use your custom function like any other built-in function.

Examples of custom functions you can create include:

  • Converting timestamps based on specific conditions or criteria.
  • Handling different timestamp formats within the same dataset.
  • Combining timestamp conversion with other data transformations or calculations.

By leveraging array formulas and custom functions through Google Apps Script, you can streamline your timestamp conversion process and handle more complex scenarios with ease.

Automate Google Sheets with Bardeen Playbooks

Converting timestamps to dates in Google Sheets can streamline data analysis and reporting. While manual methods exist, automating this process with Bardeen can save you significant time and reduce errors. Automation is especially beneficial when dealing with large datasets or when needing to perform this conversion frequently. Consider automating to enhance efficiency and accuracy in your data management tasks.

  1. Copy a list of meetings during a timeframe to a Google Sheet: This playbook automates the process of transferring meeting details from Google Calendar to Google Sheets, ideal for organizing schedules and enhancing time management.
  2. Copy a newly created Asana task to Google Sheets: Automate the synchronization of task management by copying new Asana tasks directly into a Google Sheets spreadsheet, facilitating project tracking and team collaboration.
  3. Create a ClickUp task when a Google Sheets spreadsheet is modified: This playbook ensures project data stays up to date across platforms by creating tasks in ClickUp whenever a Google Sheet is modified, optimizing project management workflows.
Contents
Automate your spreadsheets with Bardeen's GPT in Spreadsheets

Easily automate data formatting and conversion tasks in Google Sheets with Bardeen's GPT integration.

Get Bardeen free

Other answers for Google Sheets

Auto Color Code Cells in Google Sheets Guide 2024

Learn to automatically color code cells in Google Sheets using conditional formatting for better data visualization and analysis.

Read more
Disable Google Sheets Downloads: A Step-by-Step Guide

Learn how to disable downloads in Google Sheets by adjusting share permissions and protecting sheets, enhancing data security.

Read more
Download Parts of Google Sheets Easily in 5 Steps

Learn how to download specific parts of Google Sheets using the sheet's GID for efficient data sharing and analysis. Streamline your workflow today.

Read more
Download Images & Charts from Google Sheets: A Guide

Learn how to download images and charts from Google Sheets directly or from a URL list to Google Drive, including step-by-step instructions.

Read more
Download Excel from Google Sheets in Simple Steps

Learn to download Excel from Google Sheets in a few steps, ensuring file compatibility and offline access. Perfect for Excel users.

Read more
Convert Date to Month Name in Google Sheets: 4 Easy Steps

Learn how to convert numeric dates to written months in Google Sheets using TEXT function, custom formatting, and more for enhanced data presentation.

Read more
how does bardeen work?

Your proactive teammate — doing the busywork to save you time

Integrate your apps and websites

Use data and events in one app to automate another. Bardeen supports an increasing library of powerful integrations.

Perform tasks & actions

Bardeen completes tasks in apps and websites you use for work, so you don't have to - filling forms, sending messages, or even crafting detailed reports.

Combine it all to create workflows

Workflows are a series of actions triggered by you or a change in a connected app. They automate repetitive tasks you normally perform manually - saving you time.

get bardeen

Don't just connect your apps, automate them.

200,000+ users and counting use Bardeen to eliminate repetitive tasks

Effortless setup
AI powered workflows
Free to use
Reading time
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
By clicking “Accept”, you agree to the storing of cookies. View our Privacy Policy for more information.