Export to Excel from Survey123 Date and time issue

2570
8
08-26-2019 10:32 AM
JeffEisman
New Contributor III

When I export to Excel from survey123 it comes up with an incorrect time. The data is collected in EST and it is coming over in the export as UTC. I understand that a enhancement has been in the works, but hoping that there is a workaround for this.

This is inspection data that is being collected and is subject to a government audit, so if the day and time are not correct when we export it will be hard to continue to use Survey123. Thanks for any help

8 Replies
by Anonymous User
Not applicable

You could potentially use this tool if you have PRO - Convert Time Zone—Data Management toolbox | ArcGIS Desktop and this tool also exist within Desktop also.

0 Kudos
JeffEisman
New Contributor III

Thanks Mike

I could do a conversion but the odd thing with this error, it only shows up on export to a report or export to Excel which is what they use to report the data. The feature service shows the correct date.

I also recommended to the project manager for the time being to do a conversion in excel, but that evidently was not a option either. 

0 Kudos
by Anonymous User
Not applicable

I see what you are talking about now as I just tried this with one of my surveys. In this case you could probably just add in a text field that would contain the following as a calculation below. This question could be hidden and set to a "string" within the "bind::esri:fieldType".

place this within the calculation of the field to preserve the local time format-date(${Date_Observed_Frozen},'%Y%m%d  %H:%M')

Might need to consider using the once() function if the form is opened multiple times to freeze the first time recorded.

This is what happens within Survey123 Connect.

This blog will help you with the formatting to your specs.

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

Cheers!

Mike

0 Kudos
JeffEisman
New Contributor III

Mike this sounds like it will work. Thanks this is great. I will probably have to us the once() since they do open the survey more than one time. 

Is this how it would look based on your example?    

   once(format-date(${Date_Observed_Frozen},'%Y%m%d  %H:%M'))

Thanks again

0 Kudos
by Anonymous User
Not applicable

Hey Jeff,

Yes, that is how it should look and it will only complete the function once. Sorry the delayed response, but we are processing a lot of "bee" data we recorded using Survey123 for a rather large reporting effort. Our final download; which is mostly photos, is just over 8gigs. 

0 Kudos
JeffEisman
New Contributor III

No Worries  Good luck with all that processing of images, sounds like an interesting project.  Thanks again

0 Kudos
by Anonymous User
Not applicable


It just might be one of the largest bumble bee surveys ever completed east of the Mississippi River with 23,000 images of habitat and bees. Lots of fun on this one.

Good luck on your form and if you haven't thought about it, then you might want to look into joining the BETA testing group for Survey123.

Survey123 for ArcGIS 

0 Kudos
FlorenceRaimbault
New Contributor III

Hi Jeff,

If you collect the date with the Start or End type’s, another option is to use the utcoffset from the pulldata function:

pulldata("@property", 'utcoffset') and add that value to the UTC date

You can calculate the date time with the following formula:

format-date(${start} + ${utcoffset },'%Y/%m/%d %H:%M:%S')

0 Kudos