Cleaning & transforming data
Dates & times
Serial numbers, parsing, timezones, business days and grouping by month.
Start with the full guides
The long-form treatment of dates & times, with the reasoning the shorter pages assume.
- GuideWorking with Dates and Times in Excel DataHandle Excel dates correctly in Python: the serial-number model, parsing mixed date text with pandas, timezone-aware timestamps, durations, and period grouping for reports.
- GuideApplying Number and Date Formats in ExcelFormat numbers, currency, percentages, and dates in Excel from Python with openpyxl number_format codes — display-only formatting that never alters the stored value.
Step-by-step how-tos
One task each, with code you can copy, run and adapt.
- How-toBuild a 4-4-5 Fiscal Calendar with pandasCreate a retail-style 4-4-5 fiscal calendar as a date table with pandas: fiscal years that start on the Sunday nearest 1 February, periods of four, four and five weeks per quarter, the occasional 53-week year and where its extra week goes, fiscal year, quarter, period and week for every date, mapping transactions onto it, and comparing periods that all have whole weeks.
- How-toBuild a Date Table for Excel Reports with pandasGenerate a calendar table — one row per day with year, quarter, month, ISO week, weekday, fiscal year, public holidays and working-day numbers — and use it to fill days with no sales, report by fiscal period, find the last working day of each month, and write it as an Excel table that lookups, pivots and Power Pivot can use.
- How-toCalculate Age and Tenure from Dates with pandas (DATEDIF Equivalent)Compute completed years, months and days between two dates for a whole column — ages from birth dates, tenure from hire dates, contract length — matching Excel's DATEDIF. Covers why days divided by 365.25 is wrong on birthdays, 29 February, an as-of date, fractional years and tenure bands.
- How-toCalculate Business Days Between Excel Dates with pandasReproduce NETWORKDAYS, NETWORKDAYS.INTL and WORKDAY in Python: count working days between two date columns with numpy busday_count, include both ends like Excel, exclude holidays from a sheet, and add working days to a date.
- How-toCalculate Month-to-Date and Year-to-Date Totals with pandasCompute MTD, QTD and YTD figures as of a report date, running period-to-date columns that reset each month or year, the same-period-last-year comparison that cuts last year at the same day, fiscal year-to-date with an April start, and a tidy summary table for an Excel report — including the leap-day and incomplete-data traps.
- How-toCalculate the Nth Weekday of a MonthFind dates like "the second Tuesday", "the first Monday" or "the last Friday" of every month with pandas: the WOM frequency in date_range, the WeekOfMonth and LastWeekOfMonth offsets, a plain function for a single date, the equivalent Excel formula written into a sheet and checked against Python, and what to do when the date lands on a public holiday.
- How-toCalculate Working Hours Between Two TimestampsMeasure support tickets, approvals and orders against service levels in business hours: count only the time between opening and closing on working days, skip weekends and holidays, handle requests raised overnight, check a vectorised version against a simple one, flag SLA breaches and write the results to Excel.
- How-toConvert Excel Time Values and Durations in PythonExcel stores times and durations as fractions of a day, and pandas returns them as time objects, timedeltas or 1900 dates depending on the cell format. Turn them all into timedeltas, handle shifts that cross midnight, parse durations typed as text and write durations back so Excel shows hours, not "1".
- How-toConvert Dates Like 20240131, 31.01.24 and 2024031 with pandasERP 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.
- How-toConvert Text Months and Weekdays to DatesTurn the period labels people type into Excel — "Jan 2026", "September", "Sept-26", "Q3 2026", "Week 14", "FY27 P03", "Mon" — into real dates and sortable periods with pandas: explicit formats for month names, a lookup for abbreviations pandas does not know, ISO week numbers, quarters, fiscal periods with a different year start, and weekday names in order, with every unparsed label reported.
- How-toConvert Unix Timestamps to Excel Dates with pandasExports from APIs, logs and databases give times as Unix timestamps — 1790920800, or 1790920800000 in milliseconds. Detect the unit from the magnitude, convert with pd.to_datetime(unit=..., utc=True), move to the reader's time zone and drop the zone so Excel accepts it, handle zeros and text, convert back, and use the equivalent Excel formula when the conversion must stay in the sheet.
- How-toConvert Year-Week Codes to Dates with pandasTurn week codes such as 2026-W14, 202614 or "wk 14/2026" from planning and ERP exports into real dates with pandas: ISO weeks that start on Monday and belong to the ISO year, parsing with %G-%V-%u, the Monday and Sunday of each week, week 53 and which years have it, US-style Sunday weeks, Excel's ISOWEEKNUM and WEEKNUM, and going from dates back to week codes.
- How-toCreate a Shift Calendar with Rotating PatternsGenerate a shift rota for teams on a rotating pattern — such as four teams on a 2-2-3 day and night cycle — with pandas: store each team's pattern as a repeating sequence anchored to a start date, expand it to any date range, prove every day has exactly one team on days and one on nights, total hours per team per month, mark public holidays, and write a colour-coded calendar grid to Excel.
- How-toExpand Date Ranges into One Row per Day with pandasTurn rows with a start and an end date into one row per day — or per working day — to count how many people are away on each date, spread a contract's value across the months it covers, and summarise by month. With explode, a vectorised repeat for large sheets, business-day calendars and a check that nothing was lost.
- How-toFill Missing Dates in a Time Series from Excel with pandasDaily exports only have rows for days when something happened, so charts skip the quiet days and averages come out too high. Add the missing dates with reindex and date_range, fill counts with zero and balances with the last known value, do it per store, and aggregate duplicate dates first.
- How-toFind Overlapping Date Ranges with pandasCatch double bookings, overlapping contracts and clashing leave requests in a sheet of start and end dates: the overlap test, checking each record against the previous one per group, listing every overlapping pair, merging overlaps into continuous periods, finding gaps, and highlighting the clashes in Excel.
- How-toFlag Public Holidays in Excel Data with PythonMark which rows of an Excel dataset fall on public holidays, country by country and region by region, with the holidays package and pandas: holiday names per row, substitute days when a holiday lands on a weekend, regional holidays such as German states, company shutdown days, working days between two dates that skip holidays, and the effect of holidays on daily averages.
- How-toGroup Excel Data by Fiscal Year and ISO Week with pandasAssign every Excel row to a fiscal year, fiscal period and ISO week with pandas — any fiscal start month, labels such as FY2026/27, period numbers 1–12, ISO years that differ from calendar years around New Year, Excel's WEEKNUM rule, and complete weekly tables with no missing weeks.
- How-toGroup Excel Rows by Month and Quarter with pandasAggregate Excel data into monthly and quarterly totals with pandas — Grouper vs to_period, filling empty periods, fiscal years, and writing the result back as a formatted report.
- How-toHandle Daylight Saving Time in Excel TimestampsExcel stores local clock times with no time zone, so the hour that repeats when clocks go back and the hour that never happens when they go forward cause wrong durations, double-counted readings and errors in pandas. Localise naive timestamps safely with ambiguous and nonexistent options, infer the repeated hour from the order of readings, compute true elapsed durations, aggregate by day without 23- and 25-hour surprises, and write results back in a form Excel users understand.
- How-toHandle the 1904 Date System in Excel Files with PythonSome workbooks count dates from 1 January 1904 instead of 1900, so the same serial number means a date four years and one day later. Detect which system a file uses with openpyxl, convert raw serial numbers with the right origin, combine files that use different systems, and write workbooks in either system.
- How-toHandle Timezones in Excel Timestamps with PythonExcel cannot store a timezone. Convert aware datetimes to one zone, strip the offset for writing, label the sheet, and re-localise correctly when reading the file back.
- How-toParse Excel Dates into Python datetimes with pandasTurn messy Excel date columns into real datetime64 values with pandas — parse_dates on read, explicit formats, dayfirst ambiguity, coerce-and-report, and mixed-type columns.
- How-toResample Irregular Readings to Regular IntervalsTurn sensor, meter or price readings taken at irregular times into tidy hourly or daily values with pandas: resample means and their bias, time-weighted averages for values that hold until the next reading, consumption per interval from cumulative meter readings by interpolating at the boundaries, gaps left visible instead of invented, several devices at once, and an Excel report.
- How-toRound Timestamps into Time Slots with pandasBucket call-centre calls, orders or sensor readings into 15-minute, hourly or shift slots: floor, ceil and round with dt accessors, count and sum per slot, show empty slots as zero with a full slot grid, build a weekday-by-hour heatmap table, assign custom slots such as shifts, and compare with Excel's FLOOR formula and its floating-point edge cases.
- How-toHighlight Overdue Dates in Excel with openpyxlMark overdue and due-soon rows in an Excel tracker with conditional formatting that uses TODAY(), so the colours stay correct every day the file is opened — ignore blank and completed rows, order rules with stopIfTrue, add a days-overdue column and avoid the text-date trap.
- How-toHighlight Weekends and Holidays in a Date ColumnShade weekend and public-holiday rows in an Excel log or timeline with openpyxl conditional formatting: WEEKDAY-based rules for Saturdays and Sundays, a holiday list generated with the holidays package on its own sheet, rule order so holidays win, the same rules across a date header row for planners, and a recalculated check of which dates each rule catches.
- How-toKeep the Latest Record per Key When Deduplicating with pandasA customer export lists the same customer several times as their details changed. Keep only the most recent row per customer with sort_values and drop_duplicates, break ties on equal timestamps, handle dates stored as text and missing dates, and avoid groupby().last(), which quietly mixes values from different rows.
- How-toCalculate Moving Averages with pandas rollingSmooth daily sales with a 7-day moving average, per store, the way an Excel AVERAGE over a sliding range does. Learn why rolling(7) and rolling("7D") differ when days are missing, what min_periods does to the first rows, trailing versus centred windows, and how to roll within groups without scrambling rows.
- How-toEOMONTH and EDATE Equivalents in pandasReproduce Excel's EDATE and EOMONTH in pandas for whole columns — month arithmetic that clamps 31 January to 28 February, month ends that do not depend on whether a date is already a month end, a different number of months per row, first-of-month dates and the time-of-day that Excel drops.
- How-toYEARFRAC and DAYS360 Equivalents in PythonReproduce Excel's YEARFRAC with all five day-count bases and DAYS360 with the US and European methods in Python: 30/360 rules for month ends and February, actual/360, actual/365, actual/actual with its leap-year logic, a vectorised version for interest accrual tables in pandas, and a check against spreadsheet results for the awkward dates.
- How-toRead a Calendar-Style Sheet into a TableRota planners, booking sheets and leave trackers are often laid out as calendars: a month title, weekday columns, a row of day numbers and a row of entries under each. Turn such grids into one row per date with openpyxl and pandas: find each month block, pair day numbers with their entries, build real dates, skip the padding days from neighbouring months, and validate that every day of every month was read.
- How-toFill Gaps in Per-Item Time Series with a Staleness LimitCarry the last known value forward for each item in a sparse Excel time series without letting old numbers pose as current: build a complete item-by-period grid, forward-fill within each item only, cap how many periods a value may be carried, record each value's age and source period, flag stale cells, and write a price matrix to Excel that shows which figures are estimates.
- How-toJoin Rows to Effective Date Ranges with pandasLook up the price, rate or rule that was in force on each row's date: a price list with valid-from and valid-to dates joined to orders, open-ended ranges, the merge-and-filter method, merge_asof with a validity check, orders that fall in a gap, overlapping price periods that match twice, and a check that every order got exactly one price.
- How-toMatch Rows by Nearest Date with pandas merge_asofAttach the exchange rate, price or status that applied at each transaction's date — the latest one on or before it — with pandas merge_asof. Match within groups such as currency, limit how stale a match may be, choose backward, forward or nearest, and fix the sorting and dtype errors it raises.
- How-toValidate Dates Fall Within an Expected PeriodCheck the dates in a monthly Excel import before loading it: rows outside the reporting month, dates in the future, years with a typo such as 2062 or 1926, day and month swapped, numbers that are really Excel serials, text that is not a date at all, and transactions on days the business was closed — each row tagged with the reason and the likely correct date where one can be inferred.
- How-toBuild a 13-Week Cash Flow Forecast Report in Excel with PythonTurn open receivables, payables and recurring commitments into a rolling 13-week cash forecast: expected receipt dates from each customer's payment history, weekly buckets, opening and closing balances, a minimum-cash warning, and an Excel report with live balance formulas and a chart.
- How-toBuild a Deferred Revenue ScheduleSpread prepaid subscription and contract revenue over the service period with pandas: daily pro-rata recognition for contracts that start mid-month, a contract-by-month schedule, the deferred revenue balance at each month end, the monthly journal, a check that invoiced equals recognised plus deferred, and the schedule written to Excel.
- How-toBuild a Maintenance Schedule in ExcelPlan preventive maintenance for machines and vehicles with pandas: tasks due every so many days or every so many running hours, next due dates from the last service and the average daily usage, overdue jobs, every occurrence over the next twelve weeks, the weekly workload against technician hours, and a week-by-asset grid in Excel with the due jobs marked.
- How-toBuild a Purchase Order Tracking ReportTrack every purchase order line from order to receipt to invoice with pandas: combine PO, goods-receipt and supplier-invoice exports, compute what is still to arrive and what has been received but not invoiced, flag late deliveries and invoices without receipts, value the open commitments, and write a status workbook buyers and accounts payable can both work from.
- How-toBuild a Support Ticket SLA ReportMeasure a helpdesk against its service levels from a ticket export with pandas: SLA targets per priority, elapsed time in business hours that skips nights, weekends and holidays, first-response and resolution breaches, open tickets close to breaching, attainment by priority, team and week, and an Excel report with the at-risk list on top.
- How-toBuild an Accruals and Prepayments ScheduleAutomate two month-end schedules with pandas: a prepayments schedule that spreads invoices paid in advance across the months they cover by days, with the release for the month and the balance left on the balance sheet, and an accruals schedule from goods received but not yet invoiced that reverses the next month — both reconciled to the ledger and written to Excel with the journals.
- How-toBuild an On-Time Delivery (OTIF) Report with PythonMeasure delivery performance from order and shipment exports: combine split shipments per order line, count lateness in working days with holidays, score on time, in full and OTIF, break results down by carrier, customer and week against a target, and write an Excel report with the late orders listed for follow-up.
- How-toRun a Report on the Last Business Day of the MonthCron cannot say "the last working day of the month", and a plain month-end offset forgets public holidays. Schedule the job every weekday and let Python decide: find the last business day with numpy and a holiday calendar, handle the nth business day for close reporting, use the business's time zone, and catch up if the server was down on the day.
- How-toSchedule Reports Correctly Across Time Zones and DSTKeep an 08:00 Excel report at 08:00 local time all year: why a UTC cron entry drifts by an hour when daylight saving starts and ends, time-zone-aware schedules in systemd, cronie, Kubernetes and Python, the skipped and repeated hours to avoid, a guard that works on UTC-only schedulers, and report periods that are 23 or 25 hours long.
- How-toFormat Dates in Excel Cells with PythonFormat dates in Excel cells with openpyxl: write real datetime objects so Excel stores a date serial, then apply yyyy-mm-dd, mmm d yyyy, or dd/mm/yyyy display codes.
- How-toFormat Durations Over 24 Hours in Excel with PythonWrite time spent, machine run time or hours worked to Excel so 26 hours 30 minutes shows as 26:30, not 2:30 or 1. Understand how Excel stores durations, fix pandas timedelta columns that arrive formatted as whole days, use [h]:mm and [mm] formats, show decimal hours, total durations with formulas, and avoid negative-time errors.
- How-toUse a Date Axis in an Excel Chart with openpyxlPlot values against real time instead of evenly spaced labels: why irregular dates need a date axis, how to set one up in openpyxl with DateAxis and crossAx, tick units and label formats, straight lines instead of smoothed ones, when to keep a text axis, and why readings within a day need a scatter chart instead.
- How-toCreate Calendar Events from an Excel Sheet with PythonTurn a schedule kept in Excel — training sessions, deadlines, shifts, release dates — into an .ics file that Outlook, Google Calendar and Apple Calendar import: combine date and time columns, handle all-day rows with exclusive end dates, attach the right time zone, give each event a stable UID so re-imports update instead of duplicating, and split the file per person.
- How-toHow openpyxl Stores Python Values: Types, Dates and NoneWhat happens to an int, a bool, a date, a Decimal, a NumPy value or a timezone-aware datetime when you assign it to an openpyxl cell — the cell type it becomes, the number format it gets, what comes back when the file is reopened, and which values fail only when you save.
- How-toForecast a Time Series with Python in ExcelUse Python in Excel to forecast monthly sales from a table in the workbook: pull the history with xl(), give it a proper monthly date index, fit a Holt-Winters model with statsmodels, return the forecast and a simple prediction band as a spilled range, chart it with matplotlib, and know the limits — cloud execution, recalculation and FORECAST.ETS as a formula-only alternative.
- How-toWrite Dates Without the Time Part from pandas to Excelpandas writes datetime columns with a YYYY-MM-DD HH:MM:SS format, so every date shows 00:00:00. Write real dates that display as dates: convert to date objects, set datetime_format on an xlsxwriter ExcelWriter, or apply a number format per column — and avoid the openpyxl engine option that is silently ignored.
When it breaks
The errors this topic produces, with the cause explained rather than just the fix.
- FixFix Excel Serial Numbers Showing Instead of DatesYour Excel file shows 45292 where a date should be. Fix it in Python with openpyxl number formats, pandas ExcelWriter date formats, and a repair pass for existing workbooks.
- FixFix Mixed Day-First and Month-First Dates in Excel DataA date column that combines 03/04/2026 from a UK office, 04/13/2026 from a US one, ISO dates from a system and real Excel dates cannot be parsed with one setting — and pandas' format="mixed" with dayfirst=True silently swaps both unambiguous US dates and ISO dates. Classify each value, infer each source's convention from its unambiguous rows, parse with explicit formats, and flag what stays ambiguous.
- FixFix "Excel does not support datetimes with timezones" in pandasExcel cells cannot store a time zone, so pandas refuses to write timezone-aware timestamps. Decide which clock the reader needs, convert to it, drop the zone, and label the column — for columns, indexes and object columns alike.
- FixFix "OverflowError: date value out of range" When Reading ExcelDate cells that hold numbers too large or too small to be dates — a sentinel like 99999999, a yyyymmdd number formatted as a date, an amount column with the wrong format — raise OverflowError in from_excel and older openpyxl, become #VALUE! with a warning in openpyxl 3.1, NaT in pandas, or OutOfBoundsDatetime later. Find the raw values, decide what they mean, and convert them deliberately.
Follow it through a track
Prefer an ordered route? These tracks cover dates & times along the way, in the order that builds on itself.
Related topics
Subjects that keep turning up on the same pages as this one, most overlap first.
Dates & times is part of Cleaning & transforming data.