Problem with the format of excel data export

Users
1

I set up an export of my database for the output of dates in French (DD-MM-YYYY) using the functions: format-date(date(#form/Date_enqute), "%d/%m/%Y")
But I export the data in Excel, the dates are always in AAA-DD-MM format.
In addition, when the surveyor is entered, for example 01/12/2026: the data appears on export from 12/01/2026.

Thanks for your help!

2

I'll help you with this Excel data export formatting issue. Let me first check the knowledge base for information about date formatting in exports.

Thank you for reaching out! I can see you're experiencing an issue with date formatting in your Excel data exports. This is a common problem that relates to how Excel interprets date data.

The Issue

The problem you're encountering has two parts:

  1. Excel Auto-Formatting: Even though you're using format-date(date(#form/Date_enqute), "%d/%m/%Y") to format dates as DD/MM/YYYY, Excel is applying its own date formatting based on your system's locale settings.

  2. Date Reversal: When a surveyor enters "01/12/2026", the export shows "12/01/2026" because Excel is interpreting the date according to its own locale (likely switching between DD/MM/YYYY and MM/DD/YYYY formats).

Understanding format-date

The format-date function correctly formats dates as text strings for display within CommCare (like in forms or case lists). However, when data is exported to Excel:

  • If the exported value looks like a date to Excel, Excel will try to reformat it according to the computer's regional settings
  • Excel may interpret the string and convert it back to its own date format

Recommended Solutions

Option 1: Force Text Format in Excel

After exporting, you can tell Excel to treat the column as text:

  1. Select the date column in Excel
  2. Right-click and choose "Format Cells"
  3. Select "Text" as the format
  4. This should preserve your DD/MM/YYYY format

Option 2: Add Non-Numeric Characters

Modify your format-date expression to include text that prevents Excel from recognizing it as a date:

concat("Date: ", format-date(date(#form/Date_enqute), "%d/%m/%Y"))

or

concat(format-date(date(#form/Date_enqute), "%d/%m/%Y"), " ")

(adding a space at the end)

Option 3: Export Different Format

Use a format that's less ambiguous, like:

format-date(date(#form/Date_enqute), "%Y-%m-%d")

This YYYY-MM-DD format is more universally recognized and less likely to be misinterpreted.

Reference

You can find more details about the format-date function and its formatting options in the CommCare documentation: CommCare Functions - format-date

Let me know if any of these solutions work for you, or if you need further assistance!

3

Thanks for helping/

  1. In my system the parameters of my computer are set up on DD/MM/YYYY.
  2. I set up 2 exports (survey and collection) on the same commcare account and on the same computer. The export of the sample database is correct with dates in DD-MM-YYYY, whereas the "survey" database exports in YYYY-DD-MM format.