When exporting reports from the Analytics section of Experience Manager, you may notice that the time zone is set to Coordinated Universal Time (UTC) instead of your Event Time Zone. This covers all reports containing time stamps, including Check-Ins, Gamification, Lead Capture, and other related data.
Preparing the Data
After exporting the report, you first need to separate the data into the respective categories.
- Open the exported report in Microsoft Excel.
- Select the first column and go to Data > Text to Column.
- Select the "Delimited" option in the window that appears, and then choose "Comma" on the next page to neatly separate the data into individual columns.
- Click on "Finish".
Converting Time Zone Data
Now, to easily convert the time to your preferred time zone, you can follow these simple steps:
- Add two additional columns to the right of Column A (Timestamp).
These extra columns will assist in separating the data into Date and Time from the initial column.
- Enter the following formula in the first cell of Column B (Date):
=LEFT(A2;10)
- Enter the following formula in the first cell of Column C (Time):
=MID(A2;12;8)-"04:00:00"
- You can adjust the time zone difference in the formula as needed to accurately reflect the specific time zone you are working with. For example, if you are working with Pacific Standard Time, you would adjust the formula to -"07:00:00" instead.
- Right-click on the column containing the Time data (column C in this case), select "Format Cells," and choose the "Time" format option. By setting the cell format to "Time," you can easily view and analyze the converted timestamps in a clear and organized manner within the spreadsheet.
- To apply the formulas to all relevant cells, simply click and drag the lower right corner of the cell containing the formula down to the last row of data. Excel will extend the formulas to all applicable cells.
