topic Export to Excel from Survey123 Date and time issue in ArcGIS Survey123 Questions

Export to Excel from Survey123 Date and time issue

JeffEisman — Mon, 26 Aug 2019 17:32:12 GMT

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

Re: Export to Excel from Survey123 Date and time issue

Anonymous User — Tue, 27 Aug 2019 20:39:35 GMT

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.

Re: Export to Excel from Survey123 Date and time issue

JeffEisman — Wed, 28 Aug 2019 02:55:21 GMT

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.

Re: Export to Excel from Survey123 Date and time issue

Anonymous User — Wed, 28 Aug 2019 19:18:12 GMT

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

Re: Export to Excel from Survey123 Date and time issue

JeffEisman — Thu, 29 Aug 2019 01:08:49 GMT

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

Re: Export to Excel from Survey123 Date and time issue

Anonymous User — Thu, 29 Aug 2019 12:45:08 GMT

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.

Re: Export to Excel from Survey123 Date and time issue

JeffEisman — Thu, 29 Aug 2019 14:49:44 GMT

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

Re: Export to Excel from Survey123 Date and time issue

Anonymous User — Thu, 29 Aug 2019 16:01:30 GMT

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

Re: Export to Excel from Survey123 Date and time issue

FlorenceRaimbault — Tue, 03 Sep 2019 13:32:54 GMT

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')