When you download export data from our system, timestamps may appear in a format like this:
2022-03-17T12:36:26.435737000Z
This is an ISO 8601 UTC timestamp, a global reference for time. This allows our system to stay consistent across different regions. While it may look complex, it's simply a standardized format that includes the date, time, fractional seconds, and a "Z" indicating it's in UTC (Coordinated Universal Time).
Why UTC?
UTC is a global standard used to ensure consistent time tracking across time zones. You can easily convert this timestamp to your local time or extract a simpler date/time format using spreadsheet formulas; making your reports more meaningful and accurate.
Quick Spreadsheet Formulas
Let's assume your timestamp is in cell A2.
Extract the Date Only
=LEFT(A2, 10)
Result: 2022-03-17
Extract the Time Only (in UTC)
=MID(A2, 12, 8)
Result: 12:36:26
Combine Date & Time
=TEXT(LEFT(A2, 10) & " " & MID(A2, 12, 8), "yyyy-mm-dd hh:mm:ss")
Result: 2022-03-17 12:36:26
This combines the two into a single readable datetime.
Convert to Your Local Time Zone
To convert UTC to your local time, you can add or subtract the time difference.
For example, to convert UTC to Eastern Time (UTC-5 or UTC-4 during daylight saving):
=(LEFT(A2, 10) & " " & MID(A2, 12, 8)) + TIME( -4, 0, 0)
Just adjust the -4 to match your local offset from UTC.
Note: This only works if your spreadsheet tool recognizes the combined string as a valid datetime. You may need to format the cell as a Date/Time.