Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Convert Dates Like 20240131, 31.01.24 and 2024031 with pandas

ERP and mainframe exports write dates as 20240131, 31.01.24, 31-Jan-24 or day-of-year codes, and read_excel returns them as integers or text. Convert each layout with an explicit format, handle placeholders like 00000000, invalid dates such as 20240231 and two-digit years, and report what did not convert.
Dates & timesData cleaningpandas

Dates that come out of accounting systems, mainframes and older ERPs rarely arrive as dates. They arrive as 20240131 — eight digits that Excel and pandas both read as the number twenty million — or as text like 31.01.24, 31-Jan-24, or 2024031, a year followed by the day of the year. Mixed in are placeholders such as 00000000 for "no date" and the occasional impossible value like 20240231. pd.to_datetime can parse all of these, but only when told the layout; letting it guess invites day-month swaps and silent failures. This page, part of Working with Dates and Times in Excel Data, converts each layout explicitly and reports what did not convert.

Four export layouts converted to the same dates Left: the same three dates written four ways: 20240131 as a number, 31.01.24, 31-Jan-24 and the day-of-year code 2024031. Right: each parsed with its own format into the real dates 2024-01-31, 2024-02-29 and 2024-03-01. A B C 1 posting entered julian 2 20240131 31-Jan-24 2024031 3 20240229 29-Feb-24 2024060 A 1 date 2 2024-01-31 3 2024-02-29 as exported parsed explicit formats

Prerequisites

Bash
pip install pandas openpyxl

postings.xlsx has six documents with dates in four layouts: an eight-digit number, dd.mm.yy text, dd-Mon-yy text and a day-of-year code. The data includes a leap day, an impossible 31 February, a 0 placeholder, blanks and a two-digit year of 69.

Step 1 — See what read_excel returns

Python
import pandas as pd

raw = pd.read_excel("postings.xlsx", dtype={"value_date": str, "entered": str, "julian": str})
print(raw.dtypes.to_dict())
print(raw)

posting is int64: nothing about 20240131 tells pandas it is a date. The text columns are read as strings because of the dtype argument — without it, julian would also be read as an integer. Blank cells arrive as NaN either way. Reading date-like codes as text is a good default: it keeps leading zeros and stops pandas guessing.

Step 2 — Parse yyyymmdd numbers with an explicit format

Convert the integers to strings and parse them with format="%Y%m%d". Placeholders and impossible dates will not match, so use errors="coerce" to turn them into NaT — and look at them immediately:

Python
posting_text = raw["posting"].astype(str).str.zfill(8)
posting = pd.to_datetime(posting_text, format="%Y%m%d", errors="coerce")
print(pd.DataFrame({"raw": raw["posting"], "parsed": posting}))

20240229 parses — 2024 is a leap year — and 20240231 becomes NaT, because there is no 31 February. The 0 placeholder, padded to 00000000 by zfill, is also NaT. Those are two different situations — an invalid date that someone should fix, and a deliberate "no date" — and the next step keeps them apart.

Step 3 — Separate placeholders from invalid dates

Decide which values mean "no date" in this export — commonly 0, 00000000, 99991231 or blank — and treat them as missing on purpose. Anything else that fails to parse is an error to report:

Python
PLACEHOLDERS = {"00000000", "99991231", ""}

def parse_dates(text, fmt):
    text = text.fillna("").astype(str).str.strip()
    is_placeholder = text.isin(PLACEHOLDERS)
    parsed = pd.to_datetime(text.where(~is_placeholder), format=fmt, errors="coerce")
    invalid = parsed.isna() & ~is_placeholder
    return parsed, invalid

posting, bad_posting = parse_dates(posting_text, "%Y%m%d")
print("invalid posting dates:", raw.loc[bad_posting, ["doc", "posting"]].to_dict("records"))

Only D3's 20240231 is reported. D4's placeholder is a missing date, as the export intended. 99991231 — "valid forever" — is another common placeholder in master data; whether it should become missing or stay as a real far-future date depends on what the column means.

Export layouts and the format codes that parse them A reference table. 20240131 parses with %Y%m%d after converting to text. 31.01.24 parses with %d.%m.%y. 31-Jan-24 parses with %d-%b-%y. 2024031, a year and day-of-year, parses with %Y%j. 2024-01-31 parses with %Y-%m-%d. Placeholders such as 00000000 are treated as missing before parsing. Format code Note 20240131 %Y%m%d convert the integer to text first 31.01.24 %d.%m.%y two-digit year rule applies 31-Jan-24 %d-%b-%y English month abbreviations 2024031 %Y%j day of year 001-366 2024-01-31 %Y-%m-%d ISO; the safe default 00000000 placeholder set to missing before parsing

Step 4 — Parse the text layouts

Each remaining column gets its own format. %d.%m.%y and %d-%b-%y use two-digit years; %Y%j reads a four-digit year followed by the day of the year:

Python
value_date, bad_value = parse_dates(raw["value_date"], "%d.%m.%y")
entered, bad_entered = parse_dates(raw["entered"], "%d-%b-%y")
julian, bad_julian = parse_dates(raw["julian"], "%Y%j")

dates = pd.DataFrame({"doc": raw["doc"], "posting": posting, "value_date": value_date,
                      "entered": entered, "julian": julian})
print(dates.to_string(index=False))
print("invalid:", {c: int(m.sum()) for c, m in
                   {"value_date": bad_value, "entered": bad_entered, "julian": bad_julian}.items()})

Every layout produces the same calendar dates where the source agrees. %b matches English month abbreviations (Jan, Feb); exports in other languages — 31-Jän-24, 31-janv.-24 — need the month names mapped to numbers first. Day-of-year 060 in 2024 is 29 February and in 2025 would be 1 March, which %j gets right because it knows the year.

Dates with times attached

Log-style exports often append the time: 20240131143000 for 14:30 on 31 January, or a date column next to a separate HHMM time column. The same approach works — extend the format, or join the two columns into one string first:

Python
stamps = pd.Series(["20240131143000", "20240229081505"])
print(pd.to_datetime(stamps, format="%Y%m%d%H%M%S").tolist())

split = pd.DataFrame({"date": [20240131, 20240229], "time": [930, 1745]})
joined = split["date"].astype(str) + split["time"].astype(str).str.zfill(4)
print(pd.to_datetime(joined, format="%Y%m%d%H%M").tolist())

zfill(4) matters for the second form. Times are exported as numbers, so 09:30 becomes 930 and 00:45 becomes 45. The parser is lenient about digit counts, which makes the unpadded forms dangerous rather than failing: 2024013145 parses as 04:05, and 202401315 — the time 00:05 — as 3 January at 01:05, with the wrong date. Padding every time to four digits makes the string unambiguous. Keep times in the same timezone as the source system; Handle Timezones in Excel Timestamps with Python covers converting them.

Step 5 — Check the two-digit year rule

D5's value date is 05.11.69. With %y, Python maps two-digit years 69–99 to 1969–1999 and 00–68 to 2000–2068:

Python
print(dates.loc[4, ["doc", "value_date"]].to_dict())

The result is 5 November 1969, while the same document's other dates are in 2024 — almost certainly a typo for 05.11.24, or a system that meant 2069. The pivot is right for recent transaction dates and wrong for older ones: birth dates from the 1950s written as 52 would become 2052. When the rule does not fit, parse the two digits yourself and choose the century from context:

Python
def century_from_context(text, cutoff_year=2030):
    parts = text.str.extract(r"(?P<d>\d{2})\.(?P<m>\d{2})\.(?P<y>\d{2})").astype(float)
    year = 2000 + parts["y"]
    year = year.where(year <= cutoff_year, year - 100)
    return pd.to_datetime(pd.DataFrame({"year": year, "month": parts["m"], "day": parts["d"]}), errors="coerce")

print(century_from_context(raw["value_date"]).tolist())

With a cutoff of 2030, 69 becomes 1969 as before but 25 stays 2025; a plausibility check — value dates more than a few years from the posting date — is what actually catches the typo. Validate Excel Columns Before Import with pandas shows how to express range checks like that.

Step 6 — Write real dates, and the report of failures

Write the converted dates with a date format, and the invalid values on their own sheet so they can be corrected in the source system:

Python
invalid_rows = raw.loc[bad_posting | bad_value | bad_entered | bad_julian]
with pd.ExcelWriter("postings_clean.xlsx", engine="xlsxwriter",
                    datetime_format="yyyy-mm-dd", date_format="yyyy-mm-dd") as writer:
    dates.to_excel(writer, sheet_name="Postings", index=False)
    invalid_rows.to_excel(writer, sheet_name="Invalid dates", index=False)
print(pd.read_excel("postings_clean.xlsx", sheet_name=None)["Invalid dates"]["doc"].tolist())

The xlsxwriter engine applies datetime_format to every date cell, so the column shows 2024-01-31 rather than a time of midnight. Write Dates Without the Time Part from pandas to Excel explains why that option only works with xlsxwriter.

Converting export dates without losing any Five steps. Read date columns as text so nothing is guessed. Separate known placeholders such as 00000000. Parse each column with its explicit format and errors set to coerce. Report values that failed and were not placeholders. Write real dates with a date number format, and the failures on their own sheet. Read as text dtype=str Set placeholders aside 00000000, blank Parse with a format errors="coerce" Report failures not placeholders Write dates + an Invalid sheet

Common pitfalls

  • to_datetime on the integer column. An integer is read as nanoseconds since 1970, not as yyyymmdd; convert to text and pass the format.
  • format="mixed" or no format at all. It guesses per value and can swap day and month; state the format.
  • errors="coerce" without a report. Invalid dates vanish into NaT.
  • Treating placeholders as errors. Decide which codes mean "no date" and set them aside first.

Performance notes

Parsing with an explicit format uses a fast path and handles millions of values per second; format="mixed" parses each value separately and is many times slower. Converting integers with astype(str) is cheap. For huge columns of yyyymmdd integers, arithmetic — year = n // 10000, month = n // 100 % 100, day = n % 100, then pd.to_datetime on the three columns — avoids string handling altogether.

Conclusion

Read export date columns as text, set known placeholders such as 00000000 aside, and parse each layout with an explicit format — %Y%m%d for eight-digit dates, %d.%m.%y and %d-%b-%y for text, %Y%j for day-of-year codes — using errors="coerce" together with a report of what failed. Check two-digit years against context, and write real dates with a date format, with invalid values on their own sheet for correction at the source.

Frequently asked questions

Why does pandas read 20240131 as a number? Because it is one. A date written as eight digits looks like an integer to Excel and to pandas, so the column arrives as int64. Convert it to a string and parse it with format="%Y%m%d", or parse the digits arithmetically.

What does errors="coerce" do? It turns values that do not match the format into NaT instead of raising. Use it together with a report of the rows that became NaT, so invalid dates such as 20240231 are seen rather than silently dropped.

How does pandas interpret two-digit years like 69 or 24? With %y, Python maps 69-99 to 1969-1999 and 00-68 to 2000-2068. That is right for recent transaction dates and wrong for, say, birth dates in the 1950s. When the rule does not fit, parse the two digits yourself and add the right century.

What is a Julian date in an export? Usually a year followed by the day of the year, such as 2024031 for 31 January 2024. It parses with the format "%Y%j", where %j is the day of the year from 001 to 366.

More on dates & times

More on dates & times →