Fix mixed date formats (dd/mm, mm/dd) before importing

The files

Event registrations exported from several systems and pasted together. In every row, Registered means 4 March 2026 and Session date means 12 May 2026.

And the cross-border version, with a Country column:

Import date format: dd/mm or mm/dd?

A CRM import reads a date column in one format. Zoho CRM's import, for example, asks you to choose the format for each date field, and lists a date format that does not match as a reason an import fails.

So a column holding several formats has to be made into one before the upload, usually with helper columns and DATEVALUE or TEXT, and every converted cell checked by eye. Sources: Zoho's help on importing data and its import FAQ, checked on 3 October 2026.

What Sloose reads, and what it can't

Sloose reads a date column in your organisation's date order, set once under Settings, Reading files. Here is example 04's Registered column with the order set to day / month / year:

Three kinds of row, and each needs a different answer:

• Read correctly. Dates in the organisation's order, with any separator (/, - or .), and dates written in the form 2026-03-04. A time after the date is dropped, so "04/03/2026 09:30" becomes 4 March.
• Not read. A month written as a word, an Excel serial number such as 46085, "TBC". Sloose does not guess at these. Validate lists them as values your CRM will refuse, before anything is written.
• Read as another date. R-1005 was written the US way. Read day first, 03/04/2026 is 3 April. It is a real date, so no check can object to it. This is the row that does the damage.

One rule per row, by country

When the file says where each row came from, a formula can choose the order for that row.helpers.date takes the value and the order to read it in:

helpers.date(row.Registered,
  row.Country === 'United States' ? 'MM/DD/YYYY'
  : row.Country === 'Japan' ? 'YYYY/MM/DD'
  : 'DD/MM/YYYY')

You can write it yourself, or ask the AI in the formula editor to write it. Either way, the editor shows the date it makes of each row of your file before anything is written.

Example 04i's Registered column, read with the organisation's order alone, and with the formula:

The formula puts the US and Japanese rows right. Two rows are still not read: "4 Mar 2026" and the Excel serial 46085. Validate lists both. Edit those two cells, extend the formula, or leave the rows out.

If the file is still a spreadsheet, upload the spreadsheet

An Excel serial number in a CSV is a date that lost its format on the way out. Sloose reads .xlsx files directly, and a cell Excel holds as a date arrives as a date, written year first. Uploading the .xlsx, rather than a CSV saved from it, avoids this kind of row altogether.

Two-digit years

A date of birth written 14/07/58 becomes 14 July 1958, not 2058. Two-digit years follow the spreadsheet rule: 00 to 29 is the 2000s, 30 to 99 the 1900s. A year outside 1930 to 2029 has to be written in full.

Next month's file

The formula is part of the mapping, so it is saved with the import and with any template made from it. Next month's registrations start with the rule already in place, and the formula editor shows its result on the new rows.

Used here