autopopulating date in Survey123 subtracts 1 day

[JamesKelly4]

I am using the pulldata() function in the calculation field to autopopulate several survey questions based on the answer to a question regarding a confirmation number. It works great except for the date field I need populated. It will populate a date, but it is a day earlier than the value provided in the .csv file (confrimations.csv).

Here is the formula I used in the calculation field:

pulldata('confirmations','harv_date','conf', ${conf_no})‍‍

Here is an example of the confirmations.csv with only 1 row of data.

id,conf,county,harv_date,sex
10168797,14400001,Davidson,09/26/2018,male‍‍‍‍

When I enter the above confirmation number, this is how it populates the date.

Any ideas? Survey123 for ArcGIS‌ James Tedrick‌ Ismael Chivite‌ #pulldata()‌ 

[JamesTedrick · Accepted solution]

Hi James,

Are you in the western hemisphere?  If so, a likely reason you are seeing this behavior is that the date string, with no other information, is being read as a date/time combination in UTC time.  If you change the date to a datetime, you should see the time register as your UTC offset in hours before midnight (as a date is being interpreted as occurring at midnight UTC).  

One way to compensate for this is to add subtract the UTC offset from the number as interpreted as a datetime - see the attached sample.

[JamesKelly4]

That worked! Thanks again James Tedrick‌!

[DougBrowning]

I am having this issue but when passing from Collector to 123.

I have a date of 8/20/2019 in Collector but when it passes to 123 it turns into 12/31/1969.

Then I removed the time and the date works but is giving 1 day before now.  So 8/19/2010.

Then I followed this post and made the form datetime.  But now it is back to 1969 if a have time on in the web map and Aug 19 if I leave the time off still.

Any ideas?

thanks

[erica_poisson]

Hi @JamesTedrick  - 

I know this post is years old, but hoping you see this. I am having the exact date issue as @JamesKelly4  in my Survey123 form - a DOB pulled in from a CSV file is 1 day off (always the day before). I used your XLSForm above as an example but this did not work - the incorrect date is still being pulled in. Do you have any suggestions? I am really struggling with this one. 

After adding all of these new fields/calculations into my XLSForm, this is what happens when I test. The date should be November 10, 1960. 

Thank you,

[KristySnyd]

Hi @erica_poisson, did you ever find a solution to this problem? I am running into the same issue.  The date on the csv is always one day before but it is correct on the tablet.

Thank you!

[erica_poisson]

Hi @KristySnyd  - unfortunately no. I stepped away from this and have not had a chance to go back to it yet. 

Sorry! 😞 

[JamesKelly4]

James Tedrick‌ I am updating this form (the one mentioned in the original question) for this year's field season, and I was wondering if there is a way to default the harvest date to blank instead of Wednesday, December 31, 1969. When there is a match via pulldata function, it is not an issue. However, when there isn't a match and staff have to enter data by hand, the auto-populated 12/31/1969 often gets over-looked. How might I correct this? Thanks in advance!

[JamesKelly4]

Doug Browning‌ might you have any insight on my question above to James Tedrick‌? Any help is much appreciated!

[DougBrowning]

No I never did get it to work and never heard from anyone as to why.  I had to just give up and take it out of my project.

From my research it may have something to do with the timezone of the service when you publish it.  I could not republish since it was already being used so I did not test that.  Also this dataset is nationwide so I was unsure which timezone to pick.

For you one idea is to pass everything as text to a text field then calculate that text into a date field behind the scenes if you need it to be date type.

Hope that helps.