You open an Excel file and look at the date column. One row contains 14/09/2024, another says April 29, 2023, and the next one uses 2024-09-13.
As a person, you can read all three without much trouble. They are different ways of writing a date. For Python, however, mixed date formats need more care. If values are parsed incorrectly or cannot be parsed at all, you can end up with missing or incorrect dates without noticing immediately.
We could ask AI to fix the whole column in one prompt, but in this tutorial we will work step by step. First, we will inspect which date formats are present. Then we will convert them into proper dates. Finally, we will check that every value survived and that the resulting date range makes sense.
Working this way takes a few more prompts, but it keeps a person in the analysis loop. We can see what was in the spreadsheet, what Python changed, and whether anything was lost along the way.
You can open the full MLJAR Studio conversation to inspect every prompt, generated Python cell, and output used in this tutorial.
Get the data
Download the demo file from datasets-for-start and save it in:
~/Documents/customers/
The file is called customers.xlsx.
It is a sample customer list with several deliberately messy parts. In this tutorial, we will focus only on signup_date.
The dates in this column use three formats. That gives us a useful example of what happens when spreadsheets are created from several systems or edited by different people.
Open the Excel file
Start MLJAR Studio and choose Start a New AI Conversation.
Use the Read a file button at the bottom, choose Read file from computer, and select customers.xlsx.
Reading is not uploading. MLJAR Studio opens
customers.xlsxdirectly from your disk. The generated Python loads the workbook and performs the date analysis locally on your computer.
Studio reads the workbook and shows a preview.
The result tells us:
Shape: (530, 7) Columns (7): ['customer_id', 'full_name', 'email', 'country', 'signup_date', 'plan', 'monthly_spend']
We have 530 rows and seven columns. The first few signup_date values already show the problem:
14/09/2024 April 29, 2023 2024-09-13
These are all valid dates, but they use three different formats. Before converting anything, let us find out how many values use each one.
Check which date formats are present
Ask Studio:
show the different date formats in signup_date with counts
Studio generates a small function that recognizes the three expected formats:
import re def date_format(value): text = str(value).strip() if re.fullmatch(r"\d{4}-\d{2}-\d{2}", text): return "YYYY-MM-DD" if re.fullmatch(r"\d{2}/\d{2}/\d{4}", text): return "DD/MM/YYYY" if re.fullmatch(r"[A-Za-z]+ \d{1,2}, \d{4}", text): return "Month D, YYYY" return "Other / missing" format_counts = ( df["signup_date"] .map(date_format) .value_counts() .rename_axis("date_format") .reset_index(name="count") ) format_counts
The result is:
| Format | Count | Share |
|---|---|---|
YYYY-MM-DD | 430 | 81.1% |
DD/MM/YYYY | 50 | 9.4% |
Month D, YYYY | 50 | 9.4% |
| Other / missing | 0 |
The groups add up to all 530 rows:
430 + 50 + 50 = 530
This gives us a concrete baseline. If we start with 530 date values, we want to finish with 530 parsed dates.
The Other / missing group is just as important as the three recognized groups. If it contained rows, we would inspect those original values before deciding how to parse them rather than guessing at another format.
Convert the text into real dates
Now ask Studio:
parse signup_date into a proper date column
Studio generates:
import pandas as pd def parse_signup_date(value): text = str(value).strip() if "/" in text: return pd.to_datetime(text, format="%d/%m/%Y", errors="coerce") return pd.to_datetime(text, errors="coerce") df["signup_date_parsed"] = df["signup_date"].map(parse_signup_date) df[["signup_date", "signup_date_parsed"]].head()
The code checks each value. If it contains a slash, Python reads it explicitly as day, month, year. Therefore 14/09/2024 becomes September 14, 2024.
For the remaining values, pandas handles representations such as 2024-09-13 and April 29, 2023.
The converted values go into a new column, signup_date_parsed. Keeping the original column is a good cleaning habit: if something looks wrong, we can compare the source text with the parsed result without reloading the workbook.
There is another important argument:
errors="coerce"
It tells pandas not to stop when a value cannot be converted. Instead, that value becomes a missing date. This makes the conversion convenient, but it also makes verification essential.
Check that every date was parsed
Ask Studio:
how many signup_date values failed to parse, and show the min and max date
Studio generates:
summary = df["signup_date_parsed"].agg( failed_to_parse=lambda s: s.isna().sum(), min_date="min", max_date="max", ).to_frame().T summary
The result is:
failed_to_parse 0 min_date 2023-01-13 max_date 2025-01-13
There are zero failed dates, so all 530 original values were converted successfully. That matches the 530 values counted before conversion.
The date range is a second check. The earliest signup is in January 2023 and the latest is in January 2025. For a customer dataset covering roughly two years, that looks reasonable.
A zero failure count alone does not prove that every interpretation is correct. A value can be a valid date with the day and month reversed. Reviewing the range and comparing samples from each original format gives us another chance to catch that problem.
Why checking the result matters
With errors="coerce", the notebook can finish successfully even when values disappear into missing dates. That behavior is useful for cleaning, but only when followed by a check.
A generic conversion such as:
pd.to_datetime(df["signup_date"], errors="coerce")
does not state how slash dates should be interpreted. The format may also be inferred from one part of the column and applied poorly to another.
For mixed date columns, describe the formats you expect and verify the result afterward. The general habit matters more than the exact implementation: inspect, convert, then check.
Ambiguous dates need human review
Consider this date:
03/04/2024
It could mean April 3 if the file uses day-first dates, or March 4 if it uses month-first dates. Both are valid, and Python cannot determine the author's intention from the value alone.
In this demo file, slash dates follow the day-first convention, so we specify:
format="%d/%m/%Y"
With your own file, check where the dates came from. Unambiguous examples such as 25/03/2024 can provide evidence because there is no 25th month. The expected date range and documentation from the source system can help too.
When the convention remains ambiguous, keep a person in the analysis loop: ask the file owner or check the source system instead of letting AI or pandas guess. A technically successful parse can still produce the wrong business date.
A shorter way for larger files
The function above is easy to understand because it handles one value at a time. For 530 rows, that is perfectly fine.
For a larger spreadsheet, recent pandas versions can parse the whole column at once:
df["signup_date_parsed"] = pd.to_datetime( df["signup_date"], format="mixed", dayfirst=True, errors="coerce", )
format="mixed" tells pandas that individual values may use different formats, while dayfirst=True states how to interpret ambiguous numeric dates.
The verification does not change. Count failed values, inspect the range, and compare representative original and parsed values. A faster conversion should not mean skipping review.
Reuse the notebook
The prompts, generated Python, and outputs are stored in a normal Jupyter notebook. This date-cleaning process remains available after you close MLJAR Studio.
When another export arrives, load it and rerun the checks. If a new file contains dates such as Sep 14 2024 or starts exporting timestamps, the format count and failed-parse check make the change visible. You can then update and rerun the code instead of manually editing dates in Excel.
A note on privacy
MLJAR Studio works with files directly on your computer. The generated Python reads customers.xlsx from your disk and performs the date conversion locally, so you do not need to upload the workbook before Python can work with it.
There is a difference between where Python runs and where the AI model runs. With a cloud AI provider, the model may receive context such as your prompt, column names, dataframe shape, and preview values. For a real customer file containing names or email addresses, consider whether that context is confidential.
If you want the AI part to stay local too, MLJAR Studio can connect to local LLMs through Ollama, vLLM, LM Studio, Jan, and llama.cpp. In that setup, prompts, data context, generated code, and results do not need to be sent to a cloud AI provider.
What we did
We started with 530 Excel dates written in three formats: 430 YYYY-MM-DD values, 50 DD/MM/YYYY values, and 50 dates written with a month name.
We inspected those formats before changing the data, converted the values into a separate parsed column, and confirmed that all 530 dates survived. We also reviewed the resulting range and made the day-first convention explicit instead of asking Python to guess.
The most useful part is the check after conversion. A date column can look fine even when values were lost or interpreted incorrectly. Counting failed parses, reviewing the range, and keeping the original values make those problems much easier to catch.
Next steps
Continue with Handle Missing Values in Excel with Python for another focused cleaning check, or follow Clean Messy Excel Data with Python and AI to handle duplicates, missing values, dates, and inconsistent country names together.