When data is downloaded from Epicollect5, the created_at and uploaded_at fields are stored in Coordinated Universal Time (UTC).
If your local time zone is different from UTC, you must apply a conversion formula to display the correct local time in your spreadsheet (Excel or Google Sheets).
The first step is to determine the time difference (offset) between your location's time zone and UTC.
Time Zone Position
Calculation
Example
Behind UTC (West)
UTC - (Offset)
Ecuador (UTC-5): Subtract 5 hours
Ahead of UTC (East)
UTC + (Offset)
London in Winter (UTC+0): No change
IMPORTANT: You must account for Daylight Saving Time (DST) if your region observes it. The offset can change depending on the date of the timestamp. For locations that do not use DST (like Ecuador), the offset is constant.
This process works identically in Microsoft Excel and Google Sheets.
Locate the column containing the UTC timestamp (e.g., Column B).
Insert a new column next to it (e.g., Column C).
Name the new column clearly (e.g., "Uploaded At (Local)").
Use the TIME() function to create the time offset value, then add or subtract it from the UTC timestamp.
Assume the UTC timestamp is in cell B2.
Scenario
Formula
Action
If you are BEHIND UTC (e.g., UTC-5)
=B2−TIME(5,0,0)
Subtract the required hours.
If you are AHEAD of UTC (e.g., UTC+3)
=B2+TIME(3,0,0)
Add the required hours.
Copy Down: Select the cell with the formula (C2), and use the fill handle (the small square at the bottom right) to copy the formula down to the rest of the rows.
Format: Select the entire converted column.
In Excel (or Sheets): Go to the Format menu → Number (or Format Cells...) → Choose a Date and Time format that meets your reporting needs.
The new column will now display the accurate local time based on the conversion you applied.
Last updated
