How to Fix Inconsistent Dates in a CSV File

Why one column ends up with five date formats

Open your file and look at one date column. You may see Jan 5, 2025 in one row. The next row may show 5/1/2025. A third row may show 2025/1/5.

All three rows can mean the same day. Or they can mean three days. You can not tell at a glance. Your import tool can not tell at all.

This mix has plain roots. One file came from a shop tool. One file came from a form on your site. One file came from a list you typed by hand.

Each tool writes dates in its own way. Each tool thinks its way is clear. None of them check the rest of the file. So the mix grows row by row.

People add to the mix too. One team types the month as a word. One team types the month as a number. One team puts the day first. One team puts the month first.

Apps make it worse when you open the file. A sheet app may rewrite dates on save. That is part of why Excel breaks CSV files. You fix one row, save the file, and the app shifts ten more.

The file is plain text, so it holds no rule. A CSV file does not say what a date is. It just holds marks on a line. The app that reads it must guess.

Some dates are facts you can not lose. A due date drives when you bill. A start date drives when work starts. A birth date must match the right person. A ship date tells a buyer where a box is.

If a date lands on the wrong day, the harm is real. A bill can go out a month late. A task can start a month too soon. A report can put sales in the wrong month.

The good news is that the fix is dull. You pick one style for the whole column. You change each row to that style. Then you check the risky rows by hand.

You do not need skill with code. You do not need a big tool. You need a clear plan and a bit of care. This guide gives you both, step by step.

Pick one target format first

Do not fix one row at a time. First pick the one style for all rows. Then change each row to fit that style. This order saves you from new mix-ups.

The best style is YYYY-MM-DD. It is the world standard for dates. NIST uses this style in its file tips. It writes Jan 31, 2013 as 2013-01-31.

The style has four parts that help you. The year comes first, with four digits. The month comes next, with two digits. The day comes last, with two digits. Dashes split the three parts.

This style sorts dates in the right order. Sort a list from A to Z. The dates line up from old to new. No other common style does that.

Say you sort dates like 12/1/2024 and 2/3/2025. A plain sort puts them in the wrong order. It reads the first digit, not the day. With YYYY-MM-DD, that trap goes away.

This style also avoids a two-digit year. A year like 25 can mean 1925 or 2025. Four digits leave no doubt. You know the year at a glance.

Some tools ask for a style of their own. Your CRM may want month, day, then year. Your shop tool may want day, month, then year. If so, use the style that tool asks for.

The rule is not which style wins. The rule is that one style wins. One column, one style, no mix. Write your pick down before you start.

Also pick how to write blank dates. Some rows will have no date at all. Leave those cells blank if the tool lets you. Do not type zero or the word none. Fake dates cause more harm than blanks.

Save a copy of the raw file first. Put it in a safe place and do not touch it. If a fix goes wrong, you can start again. A clean copy is your way back.

Now you have a target and a safe copy. Next, look for the dates that can fool you. Those dates need your eyes, not a tool.

The risky dates: when 03/04 can mean two days

Some dates are safe to change by tool. Some dates are not safe at all. The risky ones look fine but hide a trap. You must spot them before you change a thing.

Here is the main trap. In the US, the month comes first. In much of the world, the day comes first. The same marks can mean two days.

Take 03/04/2025 as a test case. A US reader sees March 4, 2025. A UK reader sees April 3, 2025. Both readers feel sure they are right.

The gap is a full month. That is not a small slip. A due date off by a month can cost you cash. A start date off by a month can cost you trust.

Any date with both parts at 12 or less is risky. The day and month can swap with no sign. Dates like 05/06 and 07/08 all sit in this zone. Treat each one as a guess till you check it.

Some dates give you a clue for free. If one part is more than 12, it must be the day. So 13/04/2025 must be day first. And 04/13/2025 must be month first.

Use those clear rows to learn the file. If clear rows put the day first, the file is day first. Then read the risky rows the same way. This clue works if one hand wrote the file.

But files can mix hands and tools. One part of the file may be day first. The next part may be month first. If the clear rows clash, do not guess. Check the source for each block of rows.

Words can help you pin a date down. A mail or note may name the day in words. A bill may show the date in two ways. Match the row to that proof when you can.

Keep a short list of the rows you check. Write the row, the old date, and the new date. This list helps if a date looks wrong later. It also shows your work to your team.

Do not rush this part of the job. The rest of the fix is fast and dull. This part is where wrong dates slip in. Your care here is the whole point.

How to convert the dates, step by step

Now do the change, one step at a time. Work on your copy, not the raw file. Keep your target style in front of you. Stop if a step feels unclear.

Steps to follow

1. Save a copy of the raw file.
2. Sort or scan the column to group styles.
3. Mark rows where day and month can swap.
4. Set those risky rows by hand first.
5. Change the safe rows to your target style.
6. Check a few rows from each group.
7. Save the file as plain CSV text.

Group rows by style before you change them. Rows with month names are one group. Rows with slashes are a new group. Rows with dots or dashes form more groups. Each group needs its own read rule.

Read each group with its rule in mind. A slash date from a US tool is month first. A dot date from an EU tool is day first. Do not use one rule for all groups.

You can do this work in a sheet app. But sheet apps guess too, and they hide text. A safer path is a tool made for the job. You can run RowSift's date check in your browser. It spots mixed date styles in the column. Then it saves a clean CSV for you.

The table below shows how rows change. Each row starts in a mixed style. Each row ends in YYYY-MM-DD. Note the rows you must check by hand.

Look at rows two and three in the table. They use close to the same digits. Yet they land on dates months apart. That is why the source of each group counts.

Work group by group, not row by row. Change one group, then check it. Then move to the next group. This pace keeps errors small and easy to find.

When you save, save as CSV, not as a sheet file. A sheet file can store dates as hidden codes. A CSV file stores the marks you see. What you see is what the next tool reads.

Open the saved file as plain text once. Read a few dates with your own eyes. If they match your target style, the change is done. If not, fix the save step and try again.

Two-digit years and other traps

Dates hide more traps than the day swap. Most traps are small and easy to miss. Learn the main ones now. Then you can spot them fast in any file.

The two-digit year is the worst of them. A date like 1-5-25 gives you just two digits. Is the year 2025 or 1925? Most tools guess that it is 2025.

That guess fails for old dates. Birth dates can be from the last century. Start dates for old firms can too. A wrong century is a huge error. Check each two-digit year by hand.

Ask what range of years makes sense. A list of new leads should have new dates. A list of birth dates can span 100 years. Use the sense of the list as your guide.

Month names can trap you too. Short forms vary from place to place. Sept can mean the same as Sep. Some tools write month names in other tongues. If a word looks odd, check it, do not guess.

Time parts can sneak into a date cell. A cell may hold a date plus a time. Your target tool may want the date alone. Cut the time off, but check the date stays right. Late times can push a date to the next day.

Blank and fake dates form one more trap. Cells may hold a dash, a zero, or a question mark. Some may hold a date like 0000-00-00. None of these are real dates. Clear them out or flag them for the team.

Watch for dates stored as plain numbers. A tool may turn a date into a day count. You see a number like 45662, not a date. That count is how some sheet apps store days. Change it back with care, then check it.

Text marks can cling to a date cell. A space at the start can break a sort. A quote mark can make a tool read text, not a date. Trim each cell so just the date stays.

None of these traps need fear. They just need a slow look. Go trap by trap down the column. Your list of checks will catch most of them.

Check the result before you import

Do not import the file yet. First prove that the fix is right. A short check now saves a long fix later. Run these checks in order.

Checks to run

• Each date in the column has one style.
• The row count is the same as before.
• No date falls out of a sane range.
• Risky rows match your hand-checked list.
• Blank cells are still blank, not fake dates.

Scan the column from top to tail. Your eyes will catch a stray style fast. One row with slashes in a sea of dashes stands out. Fix any stray row you find.

Sort the column and look at both ends. The top should hold your oldest date. The tail should hold your newest date. If a date from 1900 or 2099 shows up, check it. It may be a two-digit year gone wrong.

Count the rows and match the count. Your fix should not add or drop rows. A lost row can mean a lost client or bill. If counts clash, go back to your copy.

While you check, you may spot the same row twice. Clean dates make dupes easy to see. This is a good time to remove duplicate rows too. One clean pass beats two messy ones.

Test the import with a small slice first. Send ten rows to your tool and see how they land. Check each date in the tool by eye. If all ten are right, send the full file.

Keep your notes with the file when you are done. Save the raw copy, the clean file, and your check list. Name each file so the next person can tell them apart. Good names end most date mix-ups for good.

You now have a calm, clean date column. Each row uses one style that sorts well. Each risky row got a check by hand. Your next import can start on solid ground.