Use this guide to understand how you can convert the time of scans displayed in the report that you export from 'Analytics > Export Scan Data' of Scanova QR Code Generator, to your local time.
The Analytics data for QR Codes records the time of scan in the Coordinated Universal Time (UTC) format. It is the primary time standard which can be used to set all time zones around the world. In order to convert the UTC time to local time, follow the given steps:
1. Ensure that the time-zone of the system is the same as the required local time-zone
For this Click Start, click Run, type timedate.cpl, and then click OK. Select the time zone to which UTC time has to be converted into from the ‘Change Time Zone’ section.
Note: This step is necessary only when the required timezone is different from the time zone of the system. Skip this step if the required time zone and the system time zone are the same.
2. Use MS Excel to convert the UTC data into the required time zone data
2.1 The Scan date and time (column G) of the downloaded analytics spreadsheet has to be trimmed as it contains some extra information. To do this, insert a column to the right of the scan_date_time column, name it Date and Time. Use the formula =LEFT(G2, 19) to get the required data. Drag down to fill the entire column.
Insert another column to the right of the Date and Time Column (column I) to paste the local time later.
2.2 Follow the given steps to open the Power Query:
2.3 In the power query editor Go to Add Column ➡ Custom Column. Fill the Dialog box as shown below and then Click OK.
Note: [Date and time] will work only if selected from the available column box. The parentheses [ ] will appear automatically on selecting Date and Time. Typing it manually will lead to an error message.
- Copy this part of the formula directly into the custom column formula box: DateTimeZone.RemoveZone(DateTimeZone.ToLocal(DateTime.AddZone()
- Then double click on Date and Time in the available column box. The parentheses [ ] will appear automatically on selecting Date and Time. (Note: The formula will not work if we manually type [Date and Time])
The formula should now look like this: DateTimeZone.RemoveZone(DateTimeZone.ToLocal(DateTime.AddZone([Date and Time] - Complete the formula by adding ,0)))
- Click on Ok
2.4 Right click the Local Time column on the top, select copy, close the query editor and paste the information in the column I which we had created in step 2.1 and had left blank to paste the local time later.