Kapiche reads the dates in your file when you upload it. Most formats are read correctly automatically, but some dates can be read two ways, for example 01/02/2024 could be 1 February or 2 January. This article explains how Kapiche reads dates, how to check what happened to your data, and how to avoid problems.
Looking to change which date column Kapiche uses for your widgets and filters? See How do I set a default date field?
Dates that are always read correctly
These formats can only mean one thing, so Kapiche always reads them correctly, whatever your project settings say:
ISO dates, which start with the year, such as 2024-02-01 or 2024-02-01 09:30:00.
Dates with the month as a word, such as 1 Feb 2024 or February 1, 2024.
We recommend using the ISO format YYYY-MM-DD wherever you can.
Dates that can be ambiguous
Dates written only as numbers, such as 01/02/2024, can be read as day first (DD/MM/YYYY) or month first (MM/DD/YYYY). Kapiche works out the order from the values in each date column:
If any value in the column has a number over 12 in the first position, such as 21/11/2022, the column must be day first.
If any value has a number over 12 in the second position, such as 11/21/2022, the column must be month first.
If nothing in the column settles it, Kapiche uses your project's date order setting (day first or month first). This one setting applies to every ambiguous date column in the project.
Tip: ambiguous columns are most common in small or short-period files, for example an export where every date falls between the 1st and 12th of the month.
How to check how your dates were read
Open your project's Project Settings. For each date column, Kapiche shows what it detected in your data, along with a few example values from that column so you can check them yourself.
What Kapiche detected | What it means |
ISO or named month | The dates can only mean one thing and were read correctly. |
Day first or month first | A value over 12 proved the order, and the whole column was read that way. |
Ambiguous | Nothing in the column settled the order, so your project's date order setting was used. Check the example values look right. |
Mixed | Values in the column disagree, for example two files that used different orders. Some dates are likely to have been read the wrong way round. |
Unknown | Kapiche couldn't tell what format the values were in. |
Note: this check is recorded when data is uploaded, so a project whose data hasn't been added to recently may not show a result yet.
You can also sense-check your dates in the Workbench:
Filter to a month you know well and compare the record count with your source system.
Look for records with dates in the future, or unexpected spikes and gaps over time.
How to avoid date problems
Use YYYY-MM-DD. In Excel, select the date column, choose Format Cells, then a custom format of yyyy-mm-dd before saving your file.
Format date columns as dates, not text. A text value such as Feb'24 can be read as 24 February. See Formatting your Data.
Use the same format in every file. If you add data to a project over time, keep the date format identical in each file. Files with different orders are the most common cause of a Mixed column.
Check exports from other tools. Some survey, contact centre and CRM tools export dates month first by default, even when your reports show them day first.
Decide how to handle bad or missing dates. You can choose to skip bad or missing dates when you create a project or in Project Settings. Skipped rows aren't loaded, so they won't appear in your record counts.
If your dates were read the wrong way round
Dates are converted when your data is uploaded, so the fix is to reload the affected data with unambiguous dates, ideally in YYYY-MM-DD format.
If record updates are enabled for your project, re-uploading rows with the same unique ID updates the existing records instead of adding duplicates. See How do I update records in my Project data?
If you're not sure, contact us using the chat icon at the bottom right of any screen. Tell us the project name and the date column affected, and we'll help you fix it.