ToolSahla, Tools Sahla

My spreadsheet dates are day/month/year. How do I convert them to 2026-01-05 style before importing?

Select the date column in Excel and apply the custom format yyyy-mm-dd, or build a new column with a TEXT formula, then save as CSV UTF-8 and import. The tool accepts YYYY-MM-DD only, and any other format is listed as an error with its row number, because 05/01/2026 could mean 5 January or 1 May.

Published · 2 min read

Why doesn't the tool understand 05/01/2026?

In the Gulf, 05/01/2026 is usually read as 5 January; in a file from a US system it may mean 1 May. If the tool guessed, it would put an expense in the wrong month without telling you, and your January and May totals would quietly be wrong. So the Expense summary generator tool rejects anything that is not year-month-day and tells you the row number.

We tried a file with four rows, and got:

  • Row 2, date 05/01/2026: 'the date is not in YYYY-MM-DD format'.
  • Row 3, date 2026-02-30: also rejected, because February has no 30th. The tool does not roll it over into March the way some programs do.
  • Row 4, date 2026-1-5: rejected, because month and day need two digits each: 2026-01-05.
  • Row 5, date 2026-01-05: accepted, and counted in month 2026-01 at 200.00 SAR.

The first line of the file is the header row, so data rows start at 2, the same number you see in Excel.

Method one: change the format

This works when Excel knows the cells are real dates: they sit right-aligned in the sheet, and their look changes when you change the format.

  1. Select the whole date column.
  2. Open Format Cells, choose Custom, and type yyyy-mm-dd as the type.
  3. Check the cells now look like 2026-01-05, with the month in the middle.
  4. Save as 'CSV UTF-8' and import the file.

Method two: a formula in a new column

If the dates are text that ignores formatting, which is common in files copied out of other systems, build a new column from the parts. With day/month/year text in cell A2, this formula gives the format you need:

=TEXT(DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)),"yyyy-mm-dd")

It assumes the day and month always have two digits, as in 05/01/2026. Fill it down to the last row, then map this new column to Date under 'Map the columns' after importing. If a row is still rejected, open that row number from the tool's message in Excel; it usually has a one-digit day or an empty cell.

Entries you type by hand into the tool's own table use the browser's date picker, so they need no conversion at all.

Common questions

Does the tool accept Hijri dates?

No, only Gregorian dates as YYYY-MM-DD. Convert Hijri dates in your sheet before importing.

Is a rejected row counted in the totals?

No. It appears in the list of rows that were not imported until you fix it and import again.