Date Format in XLSForm: DD-MM-YYYY with format-date()
To show a date as DD-MM-YYYY in XLSForm, add a calculate row with format-date(${visit_date}, '%d-%m-%Y'): a visit on 2026-10-25 becomes 25-10-2026. The date question itself always stores YYYY-MM-DD, so the formatted copy is a text value for labels, notes, IDs and exports, while the original date stays for checks and calculations. Each example below compiles with pyxform and ODK Validate, the same check ODK Central runs when you upload a form.
Can I change the format of a date question itself?
No. Two things are fixed, and neither one is set in the form:
- The stored value is always
YYYY-MM-DD, for example2026-10-25. The XForms standard behind ODK, KoboToolbox and SurveyCTO prescribes it, so every server, export and date function reads it the same way. - The picker on screen follows the phone's language and region settings, so two phones in the same team can show the same date in different orders.
What you can change in the form is which parts the picker asks for. The month-year appearance asks for month and year only, and year asks for the year only. For anything else, keep the date question and add a formatted copy next to it, as in the next section.
How do I show a date as DD-MM-YYYY?
Add a calculate row after the date question and show it in a note:
| type | name | label | calculation |
|---|---|---|---|
| date | visit_date | What is the date of the visit? | |
| calculate | visit_date_dmy | format-date(${visit_date}, '%d-%m-%Y') | |
| note | confirm_date | Visit date: ${visit_date_dmy}. Check it matches the date on the household card. |
The format string is free text with codes in it, so the separator is up to you: '%d/%m/%Y' gives 25/10/2026 and '%d.%m.%Y' gives 25.10.2026. Both the expression and the format string need quotes around them in the calculation cell, and the format string uses single quotes so it does not clash with the double quotes Excel or a CSV puts around the whole cell.
Which format codes can I use?
These are the codes in the ODK XForms function reference. format-date() accepts the date codes, and format-date-time() accepts all of them:
| Code | Gives | 25 October 2026, 09:05:30 |
|---|---|---|
%Y | 4-digit year | 2026 |
%y | 2-digit year | 26 |
%m | month, 2 digits | 10 |
%n | month, no padding | 10 (March gives 3) |
%b | short month name | Oct |
%d | day, 2 digits | 25 (the 5th gives 05) |
%e | day, no padding | 25 (the 5th gives 5) |
%a | short day name | Sun |
%H | hour, 24-hour, 2 digits | 09 |
%h | hour, 24-hour, no padding | 9 |
%M | minute, 2 digits | 05 |
%S | second, 2 digits | 30 |
%3 | millisecond, 3 digits | 000 |
There is no code for the full month name, for 12-hour time or for AM/PM. Codes are case sensitive: %m is the month and %M is the minute, so check the case of every code.
How do I show the month name, like 25 Oct 2026?
Use %e for the day without a leading zero and %b for the short month name:
| type | name | label | calculation |
|---|---|---|---|
| date | distribution_date | On what date did the household receive the food ration? | |
| calculate | ration_date_text | format-date(${distribution_date}, '%e %b %Y') | |
| calculate | ration_weekday | format-date(${distribution_date}, '%a') | |
| note | show_ration | Ration received on ${ration_weekday} ${ration_date_text} |
This shows Sun 25 Oct 2026. Month and day names follow the form's language when the app can tell what it is, otherwise the phone's language, so a Bangla or French form can show them in that language. Because the text depends on the phone, use the numeric codes in anything you will sort or analyse, and keep the names for what the enumerator reads.
A written-out date such as 25 Oct 2026 is the safest one to read back to a respondent. 05/10/2026 means 5 October to most of the world and 10 May to someone used to US dates, and that confusion is common in training.
How do I show the interview start time or today's date?
The start metadata row records a date and time, so format it with format-date-time(). today() returns a date, so format-date() is enough:
| type | name | label | calculation |
|---|---|---|---|
| start | start | ||
| calculate | start_text | format-date-time(${start}, '%d/%m/%Y %H:%M') | |
| calculate | today_text | format-date(today(), '%d-%m-%Y') | |
| note | show_start | Interview started at ${start_text}. Today is ${today_text}. |
format-date() only formats the date part. An ODK forum user who wrote format-date(${start}, '%m%d%H%M') got 0000 for the hour and minute, because the time codes need format-date-time().
To record the time a point in the form is first reached, put once() on the outside: once(format-date-time(now(), '%H:%M')). An ODK forum thread found that format-date-time(once(now()), '%H:%M:%S') can fail in Collect with a type mismatch, while the same expression with once() outside works.
How do I use a date in an ID or file name?
Use '%Y%m%d', which sorts in date order as plain text, and join it with concat():
| type | name | label | calculation | constraint |
|---|---|---|---|---|
| text | enum_code | What is your enumerator code? | regex(., '^[A-Z]{2}[0-9]{2}$') | |
| date | visit_date | What is the date of the visit? | ||
| calculate | visit_ymd | format-date(${visit_date}, '%Y%m%d') | ||
| calculate | visit_id | concat(${enum_code}, '-', ${visit_ymd}) | ||
| note | show_visit_id | Visit ID: ${visit_id} |
Enumerator KA07 visiting on 25 October 2026 gets KA07-20261025. Day-first IDs such as 25102026-KA07 look natural but sort by day of the month, not by date. For IDs that must be unique, see unique IDs in XLSForm.
Can I use the formatted date in calculations?
No. format-date() returns text, and text cannot be compared with dates or subtracted. Keep the original date for every check and calculation, and use the formatted copy only for display:
| type | name | label | calculation | relevant | constraint |
|---|---|---|---|---|---|
| date | visit_date | What is the date of the visit? | . <= today() | ||
| calculate | visit_month | format-date(${visit_date}, '%n') | |||
| calculate | days_since_visit | int(today() - ${visit_date}) | |||
| select_one yes_no | lean_season_meals | Did the household skip meals in the past 7 days? | ${visit_month} >= 6 and ${visit_month} <= 9 | ||
| note | show_days | The visit was ${days_since_visit} days ago. |
| list_name | name | label |
|---|---|---|
| yes_no | 1 | Yes |
| yes_no | 0 | No |
The constraint and days_since_visit use ${visit_date}, not the text. The one place a formatted value is useful in logic is a single part of the date: %n gives the month as a number, so the lean season question here only appears for visits from June to September. The age from date of birth guide shows more date arithmetic.
Do not put format-date() in the calculation column of the date question itself. A date question's value must stay YYYY-MM-DD; formatting it into DD-MM-YYYY text breaks every rule that reads it. Always use a separate calculate row.
Common date format mistakes and how to fix them
| You wrote | Write instead | Error you will see |
|---|---|---|
format_date(${visit_date}, '%d-%m-%Y') | format-date(...) with a hyphen | cannot handle function 'format_date' |
TEXT(${visit_date}, 'dd-mm-yyyy') | format-date(${visit_date}, '%d-%m-%Y') | cannot handle function 'TEXT' |
format-date(${visit_date}, %d-%m-%Y) | Put the format in quotes: '%d-%m-%Y' | Couldn't understand the expression starting at this point |
format-date(${visit_date}, '%d-%m-%Y' | Close the bracket | Mismatched brackets or parentheses in expression |
format-date(${visit_date}) | Add the format string | Requires 2 arguments but 1 provided |
format-date(${visit_dt}, ...) with no visit_dt row | Use the question's exact name | Could not find the name 'visit_dt' |
format-date(${visit_date}, 'dd-mm-yyyy') | '%d-%m-%Y' | No error, but the output is the letters dd-mm-yyyy |
format-date(${start}, '%d/%m/%Y %H:%M') | format-date-time(...) | No error, but the time comes out as 00:00 |
The last two upload without complaint, so preview the form and pick a date before you deploy. For other messages, see common XLSForm errors, or paste your form into the free XLSForm validator.
Does this work the same in ODK, KoboToolbox and SurveyCTO?
The format codes are the same on all three. What differs:
| Platform | What to know |
|---|---|
| ODK (XLSForm) | format-date() for dates and format-date-time() for date and time values, both in the calculation column. |
| KoboToolbox | Same XLSForm syntax. In the Formbuilder, add a Calculate question and type the expression. |
| SurveyCTO | Its documentation lists format-date-time() for both dates and times, with the same codes, for example format-date-time(today(), '%Y-%b-%e'). Skip logic goes in a column named relevance. |
If a form will run on more than one platform, format-date-time() with date codes only is the safest choice. On every platform the data export keeps the original date as YYYY-MM-DD; Excel may then show it in your computer's date style when you open the file, which is a display setting in Excel, not a change in the data.
Why format dates in the form
A formatted date in a note lets the enumerator read the date back to the respondent in a form they recognise, and catch a wrong year or a swapped day and month before leaving the house. The same value in an ID or file name saves a cleaning step later, because nobody has to reformat thousands of dates in Stata or Excel. Keeping the original YYYY-MM-DD date alongside it means none of your checks or calculations change.
You can describe it in plain language instead: "show the visit date as day-month-year in a note, and build a visit ID from the enumerator code and the date". LooprAI writes the calculate rows with the right format codes, then compiles the form with pyxform before anything is saved, so a missing quote or an underscore in format-date never reaches your form.
Next: XLSForm calculation examples →
Frequently Asked Questions
How do I change the date format to DD-MM-YYYY in XLSForm?
Keep the date question and add a calculate row with format-date(${visit_date}, '%d-%m-%Y'). The date question still stores YYYY-MM-DD, and the calculate holds the DD-MM-YYYY text you can show in notes, labels and IDs.
Why does my date question still show and store YYYY-MM-DD?
The stored value of a date question is fixed as YYYY-MM-DD by the XForms standard, and the picker follows the phone's language and region settings. The form cannot change either one, so add a separate calculate row with format-date() for display.
What is the difference between format-date() and format-date-time()?
format-date() formats only the date part, with codes such as %d, %m and %Y. format-date-time() also accepts %H, %M and %S for the time. Use format-date-time() for start, end and now() values, or the time comes out as zero.
How do I show the month name in XLSForm?
Use %b, for example format-date(${visit_date}, '%e %b %Y') gives 25 Oct 2026. It is the short name; there is no code for the full month name. Month and day names follow the form's language or the phone's language.
Can I compare or subtract a formatted date?
No. format-date() returns text, so date comparisons and subtraction on it give wrong results. Use the original date question in constraints and calculations, such as int(today() - ${visit_date}), and keep the formatted copy for display only.
How do I record the current time in a fixed format?
Add a calculate with once(format-date-time(now(), '%H:%M')). Put once() on the outside so the time is kept when the form is opened again. Writing once() inside format-date-time() can fail in ODK Collect with a type mismatch.
No Credit Card Required
Build the form first. Add hosting when you need it.
Start free with LooprAI, then deploy a managed ODK Central server or run DataSnap checks when your project is ready.
Automation handles the mechanical work.
Researchers decide what the evidence means.