EPOCHTODATEformula in Google Sheets converts an epoch timestamp into a date format. An epoch timestamp is a numerical representation of a date, measured in seconds or milliseconds from January 1, 1970 (also known as Unix time). This formula is commonly used when working with APIs or data sources that provide timestamps in epoch format.
- How to use
- Examples of using
EPOCHTODATEformula not working?
- Similar formulas to
EPOCHTODATE formula with the syntax shown below, it has 1 required parameter and 1 optional parameter:
- timestamp (required):
The epoch timestamp to convert to a date format. Must be a numerical value.
- unit (optional):
Optional. The unit of time used in the epoch timestamp. Can be either "seconds" or "milliseconds". If omitted, the default value is "milliseconds".
ExamplesHere are a few example use cases that explain how to use the
EPOCHTODATEformula in Google Sheets.
Convert API Timestamps
When working with APIs that provide timestamps in epoch format, use
EPOCHTODATE to convert the timestamp into a readable date format.
Analyze Time-Based Data
EPOCHTODATE to convert epoch timestamps into date formats for easier analysis of time-based data.
Generate Date-Based Reports
When generating reports that require date-based calculations, use
EPOCHTODATE to convert epoch timestamps into date formats for easier analysis and presentation.
EPOCHTODATEnot working? Here are some common mistakes people make when using the
EPOCHTODATEGoogle Sheets Formula:
Providing timestamp in wrong format
The timestamp parameter of the
EPOCHTODATE function must be provided in Unix timestamp format (i.e. number of seconds since January 1, 1970). Providing the timestamp in a different format will result in an error.
Providing unit in wrong format
If you provide the unit parameter, it must be one of the following values: 'seconds', 'milliseconds', 'microseconds', or 'nanoseconds'. Providing a different value will result in an error.
Not accounting for time zone differences
When using the
EPOCHTODATE function, it is important to be aware of time zone differences. By default, the function displays the date and time in the time zone of the user's device, which may not be the same as the time zone of the data being analyzed. You can use other Google Sheets functions such as
CONVERT_TZ to convert the date and time to a different time zone.
Using incorrect date and time format
By default, the
EPOCHTODATE function displays the date and time in the format 'MM/DD/YYYY HH:MM:SS AM/PM'. If you require a different format, you can use other Google Sheets functions such as
TEXT to format the date and time.
Incorrectly calculating duration between two dates
When using the
EPOCHTODATE function to calculate the duration between two dates, it is important to be aware of time zone differences. If the two dates are in different time zones, you may need to convert them to a common time zone before performing the calculation.
The following functions are similar to
EPOCHTODATE or are often used with it in a formula:
DATEVALUEformula in Google Sheets converts a date string into a date value that Google Sheets can recognize as a date. This formula is commonly used when data is imported or copied into Google Sheets from other sources that may not recognize date values. The formula can be used to convert a variety of date string formats into a date value that Google Sheets can work with.
NOWfunction in Google Sheets returns the current date and time, updated in real-time. This function is commonly used to create timestamps for data entry or to track changes made to a sheet.
TIMEVALUEformula in Google Sheets is used to convert a time string into a time value in the format of HH:MM:SS. This formula is often used to extract the time value from a date/time string and use it for further calculations or formatting.
You can learn more about the
EPOCHTODATE Google Sheets function on Google Support.