# Change the date format submitted from odk collect to google sheets (dd/mm/yy to yyyy-mm-dd) **URL:** \ **Category:** Support\ **Tags:** google-sheets, odk-collect\ **Created:** [March 3, 2020, 4:51pm UTC](https://forum.getodk.org/t/change-the-date-format-submitted-from-odk-collect-to-google-sheets-dd-mm-yy-to-yyyy-mm-dd/25260 "2020-03-03T16:51:21Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Kled](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/kled/32/9676_2.png) [@Kled](https://forum.getodk.org/u/Kled)\ **Post date:** [March 3, 2020, 4:51pm UTC](https://forum.getodk.org/t/change-the-date-format-submitted-from-odk-collect-to-google-sheets-dd-mm-yy-to-yyyy-mm-dd/25260/1 "2020-03-03T16:51:21Z")
**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.** ![date1](https://getodk.b-cdn.net/uploads/default/original/2X/6/670765ba5994341bd7c4d5e33f180a1af0c7e638.jpeg) ![date2](https://getodk.b-cdn.net/uploads/default/original/2X/e/ee7d3be25d0e8b0f12e765557e555b9b7790b254.jpeg) [TEST DATE.xlsx](https://forum.getodk.org/uploads/short-url/zZwqYERL2b5Ld5XaphSKtMGwBhX.xlsx) (9.4 KB) [TEST DATE.xml](https://forum.getodk.org/uploads/short-url/hRXXngpOhoUiBxEOYfZ9S1Mr29t.xml) (1.1 KB) ---
**Author:** ![danbjoseph](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/danbjoseph/32/21901_2.png) [@danbjoseph](https://forum.getodk.org/u/danbjoseph)\ **Post date:** [March 3, 2020, 5:16pm UTC](https://forum.getodk.org/t/change-the-date-format-submitted-from-odk-collect-to-google-sheets-dd-mm-yy-to-yyyy-mm-dd/25260/2 "2020-03-03T17:16:09Z")
To get what you're asking, I think you could have a second question of type `calculate` and use the [`format-date(date,format)`](https://docs.opendatakit.org/form-operators-functions/#format-date) function. ```plaintext +===========+=============+=======+====================================+ | type | name | label | calculation | +===========+=============+=======+====================================+ | date | my_date | Date: | | +-----------+-------------+-------+------------------------------------+ | calculate | format_date | | format-date(${my_date},'%Y-%m-%d') | +-----------+-------------+-------+------------------------------------+ ``` ---
**Author:** ![Kled](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/kled/32/9676_2.png) [@Kled](https://forum.getodk.org/u/Kled)\ **Post date:** [March 3, 2020, 5:43pm UTC](https://forum.getodk.org/t/change-the-date-format-submitted-from-odk-collect-to-google-sheets-dd-mm-yy-to-yyyy-mm-dd/25260/3 "2020-03-03T17:43:49Z")
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. ---
**Author:** ![danbjoseph](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/danbjoseph/32/21901_2.png) [@danbjoseph](https://forum.getodk.org/u/danbjoseph)\ **Post date:** [March 3, 2020, 6:37pm UTC](https://forum.getodk.org/t/change-the-date-format-submitted-from-odk-collect-to-google-sheets-dd-mm-yy-to-yyyy-mm-dd/25260/4 "2020-03-03T18:37:02Z")
I think you should also be able to adjust the cell formatting directly in Google Sheets. ![Screen Shot 2020-03-03 at 1.34.00 PM](https://getodk.b-cdn.net/uploads/default/original/2X/1/158a3affb62a2da18fb8a83a170c64361461d07d.png) ![Screen Shot 2020-03-03 at 1.32.56 PM](https://getodk.b-cdn.net/uploads/default/original/2X/f/f320d2b6db3f10a2c7856dff34343b2bb55e82a8.png) ![Screen Shot 2020-03-03 at 1.31.16 PM](https://getodk.b-cdn.net/uploads/default/original/2X/e/eeda94d9f46d090dba9544575a7367af479440fc.png) ![Screen Shot 2020-03-03 at 1.31.22 PM](https://getodk.b-cdn.net/uploads/default/original/2X/c/ceb1477ce32f725d97ea2485240be62d76d676ac.png) However, I don't know if the formatting will persist when new rows are added by ODK. ---
**Author:** ![Kled](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/kled/32/9676_2.png) [@Kled](https://forum.getodk.org/u/Kled)\ **Post date:** [March 3, 2020, 7:05pm UTC](https://forum.getodk.org/t/change-the-date-format-submitted-from-odk-collect-to-google-sheets-dd-mm-yy-to-yyyy-mm-dd/25260/5 "2020-03-03T19:05:01Z")
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. ![date3](https://getodk.b-cdn.net/uploads/default/original/2X/c/cb1dfed52f955c311dafa10a2b92661a38b725a6.jpeg) ---
**Author:** ![danbjoseph](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/danbjoseph/32/21901_2.png) [@danbjoseph](https://forum.getodk.org/u/danbjoseph)\ **Post date:** [March 4, 2020, 1:14pm UTC](https://forum.getodk.org/t/change-the-date-format-submitted-from-odk-collect-to-google-sheets-dd-mm-yy-to-yyyy-mm-dd/25260/6 "2020-03-04T13:14:36Z")
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](https://www.ablebits.com/office-addins-blog/google-sheets-change-date-format/)** > > Learn how Google Sheets formats dates and how you can change the date format, create custom formatting and convert dates to numbers and text ---
**Author:** ![Kled](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/kled/32/9676_2.png) [@Kled](https://forum.getodk.org/u/Kled)\ **Post date:** [March 4, 2020, 4:29pm UTC](https://forum.getodk.org/t/change-the-date-format-submitted-from-odk-collect-to-google-sheets-dd-mm-yy-to-yyyy-mm-dd/25260/7 "2020-03-04T16:29:43Z")
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.