1. What is the problem? Be very detailed.
I have authored and published an ODK form from Excel that is being used in ODK Collect. The submissions are going into a Google Sheet. All dates being reported from ODK Collect to Google Sheets have the day and month flipped. Today's date is December 11, 2020 but it is being entered in the Sheet as November 12, 2020.
2. What app or server are you using and on what device and operating system? Include version numbers.
ODK Collect on Android phones and tablets all with the same result
Google Sheets
3. What you have you tried to fix the problem?
I have specified the date and date time formats of the 3 date questions in the survey (start_time, end_time, date), I have confirmed the Locale is the United States (which is correct), and I have tested the Google Sheet formatting by entering an integer that get correctly displayed ( 1 = 12/31/1899 0:00:00)
I have confirmed in ODK Collect, that today() shows correctly and when I select a date with the date widget it shows correctly as well.
Based on the above, I assume the date must be coming incorrectly from ODK Collect
4. What steps can we take to reproduce the problem?
I was able to make a single question survey with ODK build that I exported to XML, loaded into ODK Collect, with a new blank Sheet and the behavior was the same.
5. Anything else we should know or have? If you have a test form or screenshots or logs, attach below.
Date-Test.xml (1.0 KB)
Hi @MattDavis939
welcome to the forum! Please introduce yourself here!
What version of ODK Collect are you using?
Did you use exactly the same form you have attached to send data to Google Sheet?
I've never heard of such problems. I tried to reproduce it but to no avail. I don't think it's a bug in ODK Collect I would rather say it might be related to formatting in Google Sheets. Have you changed maybe something in columns formatting? Could you maybe try sending data to a completly new spreadsheet?
Hello Grzegorz,
The ODK Collect version is 1.28.4 which I believe is the latest.
I have another Sheet (Project Sheet) I am using for the actual responses to our collection app. The one I used in the test app that I shared with you was brand new, no formatting of any kind, and only had a date being reported into it and it had the same problem.
In the Project Sheet I have tried various formatting setting but none change the behavior and reported, if I type in an integer to the date field, it is getting converted and displayed correctly.
I just started with ODK last week although I've used XLSForms on and off for a few months now and will introduce myself as soon as I get a chance.
Thank you in advance for any help you can provide,
Matt
and to make sure we are on the same page... are you able to reproduce your issue or maybe that happened just once?
I have reproducer it. I have my project survey and project sheet and then a test survey and test sheet that had the same behavior. I created my project survey in Excel and it uses a groups and conditional statements but the test survey which only asks for a date I created using the ODK Build site.
Ok I got it. The issue occurs if your locale in settings is United States (maybe it also occurs in case of other locations), if I create a new spreadsheet the default values is United Kingdom and then everything seems fine. I'll investigate the issue further.
1 Like
OK, so I changed my Sheet setting to Locale United Kingdom and it works OK! If I switch back and forth the behavior switches so it looks like this is Locale US issue. Maybe other Locales but I have not check that.
In the attached image I had submitted dates and they were displaying incorrectly with the Locale set to US. I switched to UK and formatted the column and it is showing correctly MM/DD/YYYY
I then switch back to US Locale and submitted a date again and that is what is in the last row. Again, formatting can't fix it, it is how the data is stored.
So my fix for now will be to set a different Locale but keep my timezone.
We use European format to send dates which is like day/month/year what might causes issues like this one.
@ln what do you think about handling it? Do you think we should just document it or maybe implement changes in Collect (for example sending dates in format we use to display them in the app should fix the issue because it's like Dec 16, 2020 and it won't cause problems with formatting)?.