Timezone change when exporting data from Survey123

[MiguelSalamanca]

Dear contributors,

There are some date questions which display on data date with time. But, when I export those data to Excel spreadsheets those fields appear with other dates and times which are different from those who are reported in survey records and it marks a time difference of 5 hours between the hour recorded and the one that appears in Excel records. How can I fix this issue? Should I format my dates? How can I set dates and times with my local timezone (GMT -05:00). Thanks a lot for your help.

[Strahanjen]

I believe that Excel exports return all dates and times in UTC. Thus, you'll need to do a manual conversion to get your desired time zone. 

[MiguelSalamanca]

Hi partner, hope you are well.

It might be right. But what I am seeing on my data is that they appear on Survey123 Web e.g. with date-time equal to March 30 19:43 while in Excel and ArcGIS Pro it is seen like March 31 00:43. Based on the user testimonial, he said that the record was submitted on the first date in the hour mentioned earlier. Is it related with the survey version?

Thanks a lot for your answer.

[MiguelSalamanca]

Hi partner, hope you are well.

It might be right. But what I am seeing on my data is that they appear on Survey123 Web e.g. with date-time equal to March 30 19:43 while in Excel and ArcGIS Pro it is seen like March 31 00:43. Based on the user testimonial, he said that the record was submitted on the first date in the hour mentioned earlier. Is it related with the survey version?

Thanks a lot for your answer.

[Strahanjen]

Yes, I see the same. Excel and Pro display times in UTC. ArcGIS Online and the field apps display the times based on the time zone of the device.

[MiguelSalamanca]

Yes, I have already made a test and start and end fields appear with UTC Time. Is it possible to change these values and make that new records appear with my local time?

Thanks again for your answer and help.

[Strahanjen]

The work around that I've seen people suggest is to add a string field to your survey and use format-date in a calculation to capture the date/time in your local time zone. I use this for capturing local time in a string column: format-date(${CollTime},'%h:%M') Maybe there is a better approach, but this works for us. 

[MiguelSalamanca]

Well, I put this picture to make clear of what is happening with the data. It is not necessary that Fecha de actividad appears with time because it is a date field but I need that wether it's filled e. g. today at 20:30 appears with date = 13/05/2022 and not 14/05/2022 2:30.

Thanks again for your answer and help provided.