how to convert timestamp to date in google sheets

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

Bardeen can automate the process of collecting data from various sources and saving it to Google Sheets, including the timestamp of when that data was collected. This allows you to easily analyze and visualize your data over time without manual data entry. Try these playbooks:

Get data from the currently opened Youtube history page and save to Google Sheets

Try it

Save AppSumo product reviews to Google Sheets

Try it

Get a list of Product Hunt products from the topic page and save them to Google Sheets

Try it

Save information from the Google Trends trending now page to Google Sheets

Try it

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.

Bardeen can help automate importing data with timestamps from various sources directly into Google Sheets, saving you time and effort:

Save the currently opened YouTube video details to Google Sheets

Try it

Get data from Amazon product page and save to Google Sheets

Try it

Copy LinkedIn company data to Google Sheets

Try it

Save eBay products from search to Google Sheets

Try it

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.

After converting a timestamp to a date, you can use Bardeen to automate extracting additional insights or copying that data to other apps. Try these playbooks to streamline your analysis and reporting workflows:

Copy a list of meetings during a timeframe to a Google Sheet

Try it

Copy all Google Calendar events during a time frame to Airtable

Try it

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.

Bardeen can also help automate tasks triggered by changes in your Google Sheets, such as generating reports or creating tasks in your project management tool:

Create a ClickUp task when a Google Sheets spreadsheet is modified

Try it

Generate a customer feedback report in a Google Doc from Google Sheets using BardeenAI

Try it

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.

Bardeen can also help you classify and save emails to Google Sheets for easy reference and analysis later:

Classify past emails and save to Google Sheets

Try it

Copy all emails from a timeframe to Google Sheets, saving all attachments in Google Drive

Try it

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

Bardeen can automate getting data that includes Unix timestamps from various sources and saving it directly to Google Sheets, where you can then use EPOCHTODATE to convert the timestamps:

Get Product Hunt product data and save it in Google Sheets every day

Try it

Save data from the Google News page to Google Sheets

Try it

Copy tweets from a profile to Google Sheets

Try it

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.

Bardeen can also save property data from Zillow, which may include Unix timestamps, directly to Google Sheets:

Save Zillow property to Google Sheets

Try it

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.

Bardeen can help automate workflows that involve copying data from various sources into Google Sheets, saving you time from manual copying and pasting. Try these playbooks to populate data into your spreadsheets:

Copy a table from Airtable to Google Sheets every day

Try it

Save links from labeled emails to Google Sheets

Try it

Get key takeaways from the currently opened Youtube video using OpenAI in a language of your choice and save the results to Google Sheets

Try it

Copy tweets from a profile to Google Sheets and translate them

Try it

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.