TL;DR: Excel stores serial numbers with 1900 leap-year legacy; Google Sheets differs in edge cases; DATEDIF modes differ from legal inclusive language. Align date system, exclusivity, and cell format before declaring mismatch.
Serial dates under the hood
Spreadsheets count days from an epoch (1900 or 1904 on Mac history). Display formatting hides serial—compare underlying values or formatted ISO text.
1900 leap year bug history
Excel treats 1900 as leap for compatibility—ancient rows near that epoch differ from modern tools by one day; rare in 2026 planning unless historical data.
DATEDIF vs between-dates
DATEDIF “D” may not match inclusive contract counting—run parallel on three-day sample in sheet and on datecalculators.org.
Cell formatted as text
“2026-01-15” text vs date serial—formulas fail silently or parse wrong locale—normalize with DATE function.
Timezone in Sheets datetime cells
Datetime stored UTC displayed local shifts calendar day—strip to date before day span.
WORKDAY vs calendar
Spreadsheet WORKDAY skips weekends/holidays; plain subtraction includes weekends—same business-day lesson as calculator modes.
Manual 30-day months in formulas
=A1+30 drift vs calendar add in tool—replace with EDATE or export endpoint from calculator.
Rounding and display
Cell shows 30.0 days but formula uses fractional datetime—display rounding hides 29.7 vs 30 calendar days.
Migration workflow
Pick 10 canonical spans in spreadsheet, replicate inputs in date tool, document delta column—if all zero after mode align, trust tool for new planning; fix legacy cells selectively.
Version control
Store ISO inputs in column A, formula in B, verified tool result in C for audit trail—C becomes source of truth when B disagrees after review.
Stop fighting the sheet
Use spreadsheets for tables and owners; use date calculator for calendar truth at boundaries—hybrid beats dogma.
Worked example: project Gantt export
MS Project duration 10d vs Excel NETWORKDAYS vs web calculator—align on one 10-day Mon–Wed sample spanning weekend; pick authoritative source for client-facing PDF footnote.
Google Sheets imported Excel with 1900 bug rows—historical project 1899 data only—modern 2026 rows should match if cells true dates.
Macro recording trap
Recorded macro used TextToColumns with locale guess—breaks on teammate machine—replace with explicit DATE(y,m,d) from ISO substrings.
Google Sheets ARRAYFORMULA date columns
ARRAYFORMULA with date serial may display differently per locale sheet owner—export CSV ISO for shared calculator verification batch.
Importrange from US sheet to EU sheet without explicit date format—silent swap—set Format → Number → ISO in master sheet.
Power Query locale
Power Query “Changed Type using Locale” step documents assumed format—missing step causes silent swap equal to manual MM/DD confusion.
LibreOffice vs Excel serial base
LibreOffice defaults 1900 system like Excel; Mac historical 1904 base still appears in legacy files—open property before comparing to web calculator batch.
CSV round-trip through Excel may convert ISO to serial—export “formatted text ISO” column for archival truth.
Python pandas to_date
pandas read_csv dayfirst=True vs False—same MM/DD confusion at dataframe layer; explicit format string ‘%Y-%m-%d’ when column is ISO.
Migration cutover weekends
Cutover “48 hour freeze” is hour math; cutover “no deploys after Friday the 15th” is calendar—runbook must not mix sentences without conversion.
Legacy Excel retirement: export critical date columns to ISO CSV, import to new system, between-dates spot-check 20 random rows against old sheet after locale normalization.
BI tools caching spreadsheet extract overnight—refresh extract after date column fix or dashboards lie until tomorrow.
Database DATE vs TIMESTAMP
Postgres DATE column compared to spreadsheet serial import—strip time zone before between-dates validation batch; TIMESTAMP at midnight UTC displays prior local day.
Excel circular references with TODAY()
Sheets using TODAY() in DATEDIF change when opened tomorrow—export values to column before sending client spreadsheet; live formulas disagree with frozen calculator screenshot from yesterday.
Google Sheets NOW() timezone follows spreadsheet owner—collaborator in Tokyo sees different “today” cell than owner in Chicago if not normalized to UTC date column.
Power BI imports Excel date serial—refresh model after fixing source column to ISO text; stale cache shows old wrong span until nightly refresh.
Airflow DAG scheduling with execution_date vs data_interval_start—off-by-one in analytics pipelines mirrors inclusive/exclusive; align naming with data engineering glossary before comparing to web calculator.
SAP ERP date fields vs Excel exports for finance reconciliation—normalize both to ISO in staging table before between-dates validation script; ERP display format misleads US eyes on EU company code.
Looker explores caching timezone—scheduled report “as of yesterday” differs for viewer; standardize report timezone in model YAML before comparing to calculator spot checks.
Snowflake TO_DATE with format mask—ETL explicit mask prevents silent swap; unit test ETL with 01-02-2026 row expecting Feb 2 or Jan 2 per declared mask, compare to web calculator same inputs.
Tableau relative date filter “last 30 days” uses rolling window not calendar month—differs from between first-of-month and today; executives comparing Tableau to calculator need same definition slide footnote.
dbt snapshot valid_from timestamps—between snapshot rows for slowly changing dimension audit, separate from marketing “days since signup” metric.
Redshift DATE_TRUNC week starts Sunday by default—override session parameter or document mismatch versus ISO Monday week in analytics catalog glossary.
