autopopulating date in Survey123 subtracts 1 day

1675
12
Jump to solution
08-19-2019 06:50 AM
JamesKelly4
Occasional Contributor

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 ArcGISJames TedrickIsmael Chivite#pulldata()‌ 

Tags (2)
0 Kudos
1 Solution

Accepted Solutions
JamesTedrick
Esri Esteemed Contributor

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.

View solution in original post

12 Replies
JamesTedrick
Esri Esteemed Contributor

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
Occasional Contributor

That worked! Thanks again James Tedrick‌!

0 Kudos
DougBrowning
MVP Notable Contributor

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

0 Kudos
erica_tefft
Occasional Contributor III

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. 

erica_tefft_0-1649769159219.png

erica_tefft_1-1649769178756.png

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. 

erica_tefft_0-1649769393122.png

Thank you,

Erica
0 Kudos
JamesKelly4
Occasional Contributor

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!

0 Kudos
JamesKelly4
Occasional Contributor

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

0 Kudos
DougBrowning
MVP Notable Contributor

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.

JamesTedrick
Esri Esteemed Contributor

Hi James, 

I would need to see the details of your form - are you using a an if() statement with the pulldata to determine if a value is blank?

The obvious method to catch this would be to place a constraint in the question to check if the value is realistic.

0 Kudos
JamesKelly4
Occasional Contributor

Hi James Tedrick thanks for getting back to me on this. I did the other day think to add a constraint so this shouldn't be an issue as far as the value passing. Is there a way to make the default value be something different than 12/31/1969? No worries if not. This one is low priority for me at this point. Thanks again!

0 Kudos