Job spreadsheet-normalise-mixed-dates-and-numbers · revised 11 October 2026
Fix mixed date and decimal formats in one spreadsheet or CSV export
Dates typed as text, day-first and month-first entries mixed together, and comma or point decimals become real dates and numbers. Ambiguous rows are listed, not guessed.
You might be seeing
- Some dates sit on the left of their cells and some on the right
- A date such as 03/04/2026 means 3 April to one person and 4 March to another
No passwords, keys, card details or admin invites needed to start.
What usually happened
A column collected from different people, offices or systems can hold real Excel dates, text that only looks like dates, day-first and month-first entries together and two-digit years. Excel reads a typed date using the computer's regional settings, so the same text can become different dates on different machines, and a text date that does not match the setting is not converted at all. Numbers can use a comma decimal in some rows and a point in others, so sums skip or misread them.
Who it’s for: An office manager, administrator or finance assistant who combines lists from several people, offices or systems and finds the dates and numbers will not sort or add up.
Usually starts when: A date column sorts in the wrong order, a sum ignores some numbers, or the same file shows different dates on different computers.
The result: Each agreed date column holds real, unambiguous dates in one agreed format, each agreed number column holds real numbers, and every row whose reading rested on a written rule or could not be decided is listed, with the rule applied or the cell left blank for you to decide.
Check whether this job fits
Answer these without sending the file. Nothing is submitted unless you choose to contact us.
Checks you can run yourself
See which entries are text
Select the date column in Excel and look at the alignment of the entries. Real dates align right by default; text dates align left. Use a COUNT of the column and compare it with the number of filled rows.
Look for: A count lower than the filled rows means some entries are text. Write down the formats you can see, such as 31/12/2025 and 12/31/2025.
What you get
- A new workbook or CSV with the cleaned columns beside the original ones
- An ambiguity sheet listing each uncertain row, the possible readings and what was done
- A rules note: the conventions applied, the cutoff for two-digit years and the output format
Included
- One workbook or CSV file of up to 20,000 rows, with up to three date columns and two number columns, delivered as a new file with your originals kept beside the new columns (a CSV gets new columns in the same file)
- Agree the rules first: which convention applies to which source, office or column where that is known, the two-digit year cutoff and the output date format
- Convert text and mixed entries into real dates and numbers by those rules, and write the result as new columns
- List every row that is ambiguous or could not be read, and every row read only because of a rule, instead of choosing silently
Not included
- Deciding the correct date for a row that is ambiguous and has no source to settle it; those rows are listed for you
- Correcting dates that are valid but wrong for your business
- Time zones, times of day, currency conversion or unit conversion
- Repairing formulas, merged layouts or duplicates; those are separate jobs
- Confidential or personal data in the first enquiry
How we know it’s done
Agreed with you before work starts. Each check produces evidence you keep.
Every non-blank cell in each agreed date column of the new file is either a real date value in the agreed format (in a workbook) or, in a CSV, text that matches the agreed format, YYYY-MM-DD unless you ask for another, and is a valid calendar date; or its row is on the ambiguity sheet.
Evidence: A count of date-valued cells (workbook) or of cells that match the format and parse as dates (CSV) against filled cells for each column, and the list of rows on the ambiguity sheet.
In an agreed reference set of at least 30 dates whose true values you confirm from source documents, every converted date equals the confirmed date.
Evidence: The reference table with row, confirmed date, converted date and result.
No row where day and month could both be valid and differ has been converted without either a written rule naming its source or a place on the ambiguity sheet.
Evidence: The rules note and a list of rows converted by rule, with the rule used for each.
For each agreed number column, the sum of the unambiguous rows in the new column equals an independent sum worked from the source text of those same rows under the agreed decimal and thousands-separator convention. The before figure is not Excel's own SUM of the original column, because SUM skips numbers stored as text. Every row that could be read two ways is listed.
Evidence: The independent source-text sum, the sum of the new column, the agreed convention for decimals and thousands separators, and the list of ambiguous number entries.
Sign-off. You check the reference dates, resolve the ambiguity sheet with us and sign off in writing. Payment follows sign-off.
If it fails. If the agreed checks do not pass, you do not pay for this fixed scope. If the file turns out to be mostly ambiguous or to need time zones, we explain why, hand back what we have and agree whether to re-quote or stop. No surprise work.
When it fits, and when we stop
It fits when
- You can say, for each source or column where it is known, which date order and decimal symbol was used, or you accept that the other rows will be listed as ambiguous
- You are allowed to share the file with a contractor, and it holds no personal data about individuals
- A named person can resolve the ambiguity sheet and sign off
We stop and tell you if
- Most rows are ambiguous and no source column or rule exists to settle them, so most of the file would end up on the ambiguity sheet
- The file mixes several time zones or times of day that matter to the result
- You want us to decide the correct date for each ambiguous row by judgement
What could go wrong
Your original columns stay in the file beside the new ones, and the original file is untouched, so you can go back to them at any time.
Scroll the table sideways to read it all.
| Risk | How we handle it |
|---|---|
| A date is read as the wrong day and month and still looks valid. | A row is converted only when it has a stated source or rule, or when only one reading is possible; any row that could be read two ways without a rule goes on the ambiguity sheet. |
| A number such as 1,234 is read as one thousand two hundred and thirty-four or as 1.234. | The decimal convention is agreed per source, and any entry that could be read either way is listed. |
| Dates in the whole workbook are offset because it uses a different date system. | We check for a shift of exactly 1,462 days between Excel's two date systems and report it. |
A reviewer who did not do the conversion re-reads a sample of rows against the rules note and checks every row on the ambiguity sheet. You decide the ambiguous rows and sign off.
How we deliver
We arrange the work and independent review, then show you the result against the agreed checks. You keep authority over your systems.
- Agree the conventions per source, the two-digit year cutoff and the output format in writing
- Classify every entry in each agreed column as real value, readable text, ambiguous or unreadable
- Convert by the agreed rules into new columns, keeping the original column beside them
- List every ambiguous, unreadable or rule-dependent row on the ambiguity sheet
- Check the owner-confirmed reference dates, check that every date cell meets the format check, and reconcile the independent source-text sums for the unambiguous number rows
- Have an independent reviewer repeat the checks on a different sample, then hand over
This is a one-off job, not a subscription. We confirm eligibility, the total price, a start window and a delivery date before you accept. Work starts only after the agreed files, the secure handling route and the acceptance checks are written down. An enquiry creates no charge or booking. We give no legal, medical, financial or tax advice.
Need to keep it working?
If a new file with the same problem arrives each period, the clean-up can be part of a recurring report pack. That is a separate quoted agreement.
Ongoing work is separately scoped and quoted: no monitoring, response-time guarantee or automatic subscription is included in this job.
Explore an ongoing engineering lane, or mention the responsibility you need in your enquiry.
What you can check
This is a new service. We have not delivered this job for a client yet.
Other ways to get this done
- For a modest number of text dates in one regional format, Excel's DATEVALUE function or the Text to Columns tool can convert them. Check the result on a few dates you know. support.microsoft.com
- If you control the source, ask for dates in the ISO form year-month-day, which cannot be read two ways.
Questions
Can you tell which is right when 03/04/2026 could be March or April?
Only if you can tell us the source or a rule. Otherwise the row goes on the ambiguity sheet for you to decide. We do not guess.
Will my original dates be overwritten?
No. The cleaned dates go in new columns next to the originals.
Do you handle times and time zones?
No. This job covers calendar dates and number formats.
Send an enquiry
Send us
- The columns involved, an approximate row count, and which sources or offices fed the file
- Which regional date order and decimal symbol each source is believed to use, if known
- Three or four invented example values for each kind of problem; no real file in the first enquiry
Later, once you agree
- The file through the secure handover route we agree in writing before any file moves, with confirmation that it holds no personal data and that you may share it
- Your written conventions per source or column, and a set of dates whose true values you can confirm from source documents
- A named person to resolve the ambiguity sheet
You keep ownership of every file we work on and of what we return. No upload portal exists yet, and the first enquiry never includes documents: send a description, counts and an invented or redacted sample only. Work can start only after we have agreed with you, in writing, a secure way to hand the files over, who may see them, how long we keep them and how we delete them. If we cannot offer a way you are comfortable with, we decline and nothing is charged. Files may be handled by automated software, including services run by other companies, and we tell you which in writing before any file moves. We work on copies, never on your only original, and we do not send, publish or contact anyone for you.
Email fallback: open your mail app
If website submission is unavailable, review and send the fallback email yourself. An email fallback is not a website receipt. Or write to hello@syntheticindustry.ai with “spreadsheet-normalise-mixed-dates-and-numbers” as the subject.