Change the date format submitted from odk collect to google sheets (dd/mm/yy to yyyy-mm-dd)

[Kled]

1. What is the problem? Be very detailed.
The date format submitted from a "date" type question from odk collect to google sheets is of the format dd/mm/yy. Is there a way to change this date format to yyyy-mm-dd from the odk collect submission itself. I know you can add another calculate format-date question but this would create a lot of extra columns because I have a lot of date questions in the survey.

2. What app or server are you using and on what device and operating system? Include version numbers.
ODK Collect v1.25.2

3. What you have you tried to fix the problem?
Created a new spreadsheet with different appearance attributes.

4. What steps can we take to reproduce the problem?
Submit a form to google sheets.

5. Anything else we should know or have? If you have a test form or screenshots or logs, attach below.

TEST DATE.xlsx (9.4 KB)
TEST DATE.xml (1.1 KB)

[danbjoseph]

To get what you're asking, I think you could have a second question of type calculate and use the format-date(date,format) function.

+===========+=============+=======+====================================+
|   type    |    name     | label |            calculation             |
+===========+=============+=======+====================================+
| date      | my_date     | Date: |                                    |
+-----------+-------------+-------+------------------------------------+
| calculate | format_date |       | format-date(${my_date},'%Y-%m-%d') |
+-----------+-------------+-------+------------------------------------+

[Kled]

I know that is an option but I was wondering if there is a way to change the date format without using the calculate type because there would be a lot of extra columns in google sheets since I have a lot of date questions. Is dd/mm/yy the default date format odk collect uses? The reason is that I am using the query function in google sheets to pull data from the submission sheet into another spreadsheet but the query function only works properly with date format yyyy-mm-dd.

[danbjoseph]

I think you should also be able to adjust the cell formatting directly in Google Sheets.

However, I don't know if the formatting will persist when new rows are added by ODK.

[Kled]

When I try to format the date columns with google sheets itself as you showed, not all the cells change to the necessary format as seen in the screenshot for cell A2. Row 4 is an entry I submitted after I formatted the columns to the date format which showed that the date 20/03/20 is not changing to the yyyy-mm-dd format but other dates are.

[danbjoseph]

That is challenging. Maybe try reading over this blog post and see if any of its suggestions are helpful:

How to change date format in Google Sheets and convert date to number and text

[Kled]

Thank you for your help. The solution to this issue was to change the spreadsheet settings locale to "United Kingdom" so that google sheets can convert the date format correctly.