nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Export to Excel from Survey123 Date and time issue

JeffEisman

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

Find more posts tagged with

Accepted answers

All comments

FlorenceRaimbault

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

JeffEisman

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

Anonymous User

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

JeffEisman

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.

Anonymous User

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.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.