I'm trying to change the date format from the default YYYY-MM-DD to DD/MM/YY but all my efforts are in vain. Ideally would like the display to look like DD/MM/YY, if not then the result.
I'm using the following under Calculation column.
format-date (date, '%e/%n/%y')
Can someone suggest where I'm going wrong.
You'll need to have a second calculate type question. The word "date" in the function needs to be changed to a data reference that pulls in the answer from your date type question. If you want the day and month to be zero-padded (that is, 06/07/18 instead of 6/7/18), then you'll want to change '%e/%n/%y' to '%d/%m/%y'. If you're using Microsoft Excel and it recognizes the cells as being dates, it may auto-format and you'll need to use the cell formatting options to adjust the appearance.
+===========+================+===============+====================================+
| type | name | label | calculation |
+===========+================+===============+====================================+
| date | my_date | Input a date: | |
+-----------+----------------+---------------+------------------------------------+
| calculate | formatted_date | | format-date(${my_date},'%d/%m/%y') |
+-----------+----------------+---------------+------------------------------------+
Also, welcome to the ODK forums! I'd be great if you could please take a minute to add a profile picture and introduce yourself.
Hi there
Sorry for waking up this question but i have the exact same problem,
this is ok when we have date question in the form but what if i have hidden questions like:
Start - changed from 2018-09-16T16:46:38.945+04 to this: 16/09/18 16:35
End
Today - changed from 2018-09-16 to this: 16/09/18
i make this changes in form but still not working,
| type | name | label | hint | required | appearance | relevant | calculation |
|---|---|---|---|---|---|---|---|
| start | start | ||||||
| end | end | ||||||
| username | username | ||||||
| today | today | ||||||
| calculate | formatted_date | format-date(${today},'%d/%m/%y') |
Hi @nodari, I tried this:
+-----------+-----------------+------------+-------------------------------------+
| type | name | label | calculation |
+-----------+-----------------+------------+-------------------------------------+
| date | date | Date: | |
| dateTime | datetime | Date time: | |
| start | start | | |
| end | end | | |
| today | today | | |
| calculate | format_date | | format-date(${date},'%d/%m/%y') |
| calculate | format_dateTime | | format-date(${datetime},'%d/%m/%y') |
| calculate | format_start | | format-date(${start},'%d/%m/%y') |
| calculate | format_end | | format-date(${end},'%d/%m/%y') |
| calculate | format_today | | format-date(${today},'%d/%m/%y') |
+-----------+-----------------+------------+-------------------------------------+
and got this (note that this was an export from kobo because that's what I had quick access to):
+-------------------------------+-------------------------------+------------+------------+-------------------------------+-------------+-----------------+--------------+------------+--------------+----------+--------------------------------------+---------------------+--------+
| start | end | today | date | datetime | format_date | format_dateTime | format_start | format_end | format_today | _id | _uuid | _submission_time | _index |
+-------------------------------+-------------------------------+------------+------------+-------------------------------+-------------+-----------------+--------------+------------+--------------+----------+--------------------------------------+---------------------+--------+
| 2018-09-17T09:24:16.878-04:00 | 2018-09-17T09:24:28.121-04:00 | 2018-09-17 | 2018-09-18 | 2018-09-19T09:30:00.000-04:00 | 18/09/18 | 19/09/18 | 17/09/18 | 17/09/18 | 17/09/18 | 31664559 | 602d755f-7df3-4c8d-be50-047e9387a304 | 2018-09-17T13:24:33 | 1 |
+-------------------------------+-------------------------------+------------+------------+-------------------------------+-------------+-----------------+--------------+------------+--------------+----------+--------------------------------------+---------------------+--------+
It seems to work. What does your data export look like? Is the field empty or incorrect or are you receiving some error?
Hello
@danbjoseph i just tested this, and no success, this is the form:
| type | name | label | hint | required | appearance | relevant | calculation |
|---|---|---|---|---|---|---|---|
| start | start | ||||||
| end | end | ||||||
| username | username | ||||||
| today | today | ||||||
| calculate | format_today | format-date(${today},'%m-%d-%Y') |
and this is result:
start | end | username | today | format_today
17/09/18 14:41 | 19/09/18 13:16 | username | 17/09/18 |
sorry, it's not formatted, but shortly, not format date comes in the field
in your submission, start is like this: 2018-09-17T09:24:16.878-04:00 but when i receive submissions on the latest version of ODK its like this: 19/09/18 13:06, same for end, 
I think your issue might be related to this change that will hopefully be out soon:
when I stuck in such troublesome situation, I searched a lot about the reason because of which I render this issue and best ways to resolve it. At that time, I have come to know that date formula not working in Excel issue arises due to following reasons. Here the reasons are:
Cause #1: Cells are formatted as text
Cause #2: The workbook is set to Manual calculation mode
I have resolved this Date Format Not Changing In Excel issue trying out this solution. You can also make it a try:
• First of all, open the Excel worksheet
• Now make a right-click on the top of the Excel column and choose the Format Cells menu
• After choosing up the Format Cells, a Window of format cell will get open on your screen.
• Here, choose the Date option and select the date format that you needed for that particular column.
• Finally, make a tap on the OK option to save the format change and then close the Window.
follow this step you will surely be able to solve your date format problem in excel.