Understanding the timestamp from the Issues export

SpeakUp provides users the option to download data of their Issues overview. If you are not familiar how, please read: How do I download an overview of Issues?

By default your issues export will contain the following dates:

• Created at

• Updated at

• Data retained until

• Acknowledged at

• Received at

• Due date

All dates (except for ‘Data retained until’) are timestamps in ISO 8601 format.

Let’s look at an example:

• 2021-10-11: Date in YYYY-MM-DD format, indicating the year (2021), month (October), and day (11th).

• T: Separator between the date and time components.

• 07:22:58: Time in HH:MM:SS

  format (24-hour clock), representing hours (7), minutes (22), and seconds (58).

• Z: Indicates the UTC (Coordinated Universal Time) timezone. "Z" stands for "Zulu" time, which is the military name for UTC.

In summary, this timestamp represents October 11, 2021, at 07:22:58 UTC.

To convert the timestamps in excel: 

1. Insert a column next to the timestamp you want to adjust; for example add a column to the left of 'Created at'. 
2. In your new column (now column B) add the following formula: =DATEVALUE(LEFT(C2,10))+TIMEVALUE(RIGHT(LEFT(C2,19),8))
3. C2 is the original 'Created at' timestamp column, you might need to adjust this value accordingly. 
4. Once the formula is in place you will need to adjust the format of the new column from 'General' to 'Date'
5. The result will look like this: