Survey 123 not exporting correct time to excel

3702
6
10-15-2018 07:38 AM
NancyLarson
New Contributor II

Hi there,

I've created a survey in Survey 123 and have selected DateTime as a field type and am having trouble getting the correct time to show up when I export the collected data to an excel file.  In survey 123 i'm seeing the correct time but when i export to excel it shows 5 hours off.  I have tried putting the pulldata function into survey 123 but perhaps i'm missing something here.  I've attached a few files of what my excel spreadsheet for my survey looks like as well as the exported output excel file.  All I want is the correct date and time for the data collected. I'm assuming theres something wrong with my formula or the way it "talks" to the DateTime field.  

Any input or guidance would be much appreciated.

Thanks,

Levi

6 Replies
ClayDonaldsonSWCA
Occasional Contributor II

Have you tried changing the type from hidden to note? 

As in, are you sure the conversion is working in survey123?

I have had more success with the decimal-date format as mentioned in this article:

https://community.esri.com/groups/survey123/blog/2016/04/17/dates-and-time-in-survey123-for-arcgis 

0 Kudos
NancyLarson
New Contributor II

I have tried changing from hidden to note and calculation and still keep getting the wrong time.  From the attached you can see that the correct time is appearing in Survey 123 but when i export to excel the time is 5 hours off.  I'm still not sure how to convert to decimal-date format either.

Thanks,

0 Kudos
JamesTedrick
Esri Esteemed Contributor

Hi Nancy,

The time stored in Survey123, as well as the rest of ArcGIS Online, is UTC time; while browsers automatically compensate for that when displaying data, other applications and the export display the value as stored (as if there are no time zones).  If you are using Survey123 Connect, you can include a question to retrieve a device's time zone, which you can then use to offset the time.

Work with date fields—ArcGIS Online Help | ArcGIS 

https://community.esri.com/groups/survey123/blog/2016/04/17/dates-and-time-in-survey123-for-arcgis 

Prepopulate answers—Survey123 for ArcGIS | ArcGIS (how to get the UTC offset)

JessicaBowser
New Contributor

When adding the UTC offset to an existing survey will it correct data that is already in the feature layer when exporting or only new data moving forward? 

0 Kudos
WinleyDurham
New Contributor II

Nancy, did any of these work for you? Having the same issue, but mine is 8 hours off. Thanks. 

0 Kudos
NancyLarson
New Contributor II

Winley, I am not sure what you are asking. I am not familiar with having this issue?

Nancy Larson, Financial & Operations Manager | Mississippi Valley Conservancy | 608.784.3606 Ext. 1

www.mississippivalleyconservancy.org<http://www.mississippivalleyconservancy.org/> | The Land. Our Future.

To Conserve native habitats and farmlands in the Driftless Area for the health and well-being of current and future generations.

0 Kudos