The dates in our reports (e.g., publishing and subscribing) use standard ISO time format:
YYYY-MM-DD:hh:mm:ss.SSSZ
where Z indicates the UTC or "Zulu" time zone.
UNIX Epoch
This will give you the time in seconds since January 1, 1970 (UNIX Epoch), which can either be used for your calculations or converted to a different time and date format.
For example, 2021-01-16T00:21:24.355Z results in 1610756484.
You can double-check the calculation by entering the value into an online epoch validator and verifying that it matches the value provided in the report.
Make sure the validator is set to use UTC as the basis for the human-readable output.
For example, in the Publishing report, the StartTimestamp (currently column Q) formula would be:
=(DATE(LEFT(Q2,4),MID(Q2,6,2),MID(Q2,9,2))+TIME(MID(Q2,12,2),MID(Q2,15,2),MID(Q2,18,2))-25569)*86400
and the EndTimestamp (column R) formula would be:
=(DATE(LEFT(R2,4),MID(R2,6,2),MID(R2,9,2))+TIME(MID(R2,12,2),MID(R2,15,2),MID(R2,18,2))-25569)*86400
Excel Date or Time
This is similar to the above, but the output will depend on whether you have the Excel cell set to be a Date or Time. To convert this timestamp into something that can be manipulated in Excel, you can use an Excel formula such as:
=(DATE(LEFT(Q2,4),MID(Q2,6,2),MID(Q2,9,2))+TIME(MID(Q2,12,2),MID(Q2,15,2),MID(Q2,18,2)))
This can be validated by comparing it to the input timestamp, as it is more human-readable than the UNIX epoch.
Instructions
Copy the formulas into the last columns of the downloaded report.
Copy the formulas down the columns and make sure that the cell references increment correctly (e.g., cell AK34 references cell Q34).
Use the values to find durations, convert to other time formats, and so on.