autopopulating date in Survey123 subtracts 1 day

351
11
Jump to solution
08-19-2019 06:50 AM
Highlighted
New Contributor III

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)
Reply
0 Kudos
1 Solution

Accepted Solutions
Highlighted
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

11 Replies
Highlighted
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

Highlighted
New Contributor III

That worked! Thanks again James Tedrick‌!

Reply
0 Kudos
Highlighted
Frequent Contributor II

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

Reply
0 Kudos
Highlighted
New Contributor III

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!

Reply
0 Kudos
Highlighted
New Contributor III

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

Reply
0 Kudos
Highlighted
Frequent Contributor II

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.

Highlighted
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.

Reply
0 Kudos
Highlighted
New Contributor III

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!

Reply
0 Kudos
Highlighted
New Contributor III

James Tedrick‌ please disregard above question. Brain fart. Obviously I could set default date today() or something like that. What I meant was, is there a way to make it default to blank? Sorry for confusion. Thanks again!

Reply
0 Kudos