The difference between two dates relies on a mechanism that spreadsheets hide behind their interfaces: each date is stored as a sequential integer. Excel uses the 1900 system (January 1, 1900, equals 1), while Google Sheets uses the 1899 system. A straightforward subtraction between two date cells thus returns a gap in days, without any special function. All the complexity arises when we want to express this gap in months, years, or working days.
Date Serialization and the 1900 System Pitfalls in Excel
Excel inherits a historical bug from Lotus 1-2-3: February 29, 1900, is considered a valid date even though 1900 is not a leap year. This phantom day shifts all serial numbers prior to March 1, 1900, by one unit.
Related reading : How to Choose the Right Colors for Your Joinery: The Great Debate Between White and Anthracite
In practice, this shift only affects calculations involving dates before March 1900, a rare case. We recommend keeping it in mind when handling historical archives or importing files between Excel and a system that does not apply this same bias (SQL databases, Python datetime).
Google Sheets circumvents the problem by adopting the 1899 system, where December 30, 1899, equals 0. During a file transfer between the two spreadsheets, a one-day discrepancy may appear on older dates. Manually verifying a known reference date before any bulk calculation helps avoid silent errors. To delve deeper into these mechanisms, you can discover solutions on Anekdotes in a detailed context.
Further reading : Discover how to easily and quickly convert a Kadéos check into a card
DATEDIF, YEARFRAC, and Direct Subtraction: Which Formula for Which Need
Direct subtraction (end cell – start cell) remains the most reliable method for obtaining a number of days. The result is an integer, without ambiguity. For weeks, divide by 7 and truncate.
The DATEDIF function accepts three arguments: start date, end date, and a unit (“d” for days, “m” for months, “y” for years). It also allows combinations like “ym” (remaining months after counting years) or “md” (remaining days after counting months). Microsoft explicitly states that DATEDIF can produce incorrect results in certain scenarios, particularly with the “md” argument over ranges covering a short month change (February).
YEARFRAC Combined with TRUNC
To obtain a decimal number of years between two dates, YEARFRAC offers a more transparent alternative. It accepts a calculation base parameter that modifies the day counting convention:
- Base 0 (US 30/360): each month is counted as 30 days, the year as 360. Used in finance for bonds.
- Base 1 (Actual/Actual): actual days over actual year. The most intuitive choice for calendar calculations.
- Base 3 (Actual/365): actual days over 365, ignoring leap years. Common in banking accounting.
By nesting YEARFRAC in TRUNC, we isolate the integer part to obtain a number of complete years. TRUNC(YEARFRAC(start;end;1)) returns the full years elapsed, without rounding.
Calculating Working Days with NETWORKDAYS and NETWORKDAYS.INTL
Counting calendar days is not enough in a professional context. The NETWORKDAYS function automatically excludes Saturdays, Sundays, and holidays listed in a reference range.
Its variant NETWORKDAYS.INTL allows redefining the weekend. The second argument accepts a numeric code or a 7-character binary string (one for each day of the week). A “1” indicates a day off. For example: “0000011” designates Saturday and Sunday as the weekend, “0000010” only removes Saturday.
This flexibility covers contexts where the weekend differs from the Western standard. We regularly observe errors with this parameter when set manually: inverting a single bit in the string falsifies the entire count.
Managing the Holiday Range
NETWORKDAYS expects an optional range of holiday dates. Two best practices:
- Store holidays in a dedicated tab, explicitly named (“Holidays_2025”), to simplify annual maintenance.
- Use a dynamic named range (OFFSET + COUNTA or a structured table) so that adding a holiday is accounted for without modifying the formula.
- Ensure that holiday dates are in date format and not text, or risk an silently incorrect count.
Automating Date Difference Calculation in No-Code Tools
No-code platforms now integrate native modules for calculating durations between two dates. Make (formerly Integromat) offers a “Date & Time” module with a configurable difference operation in days, hours, or minutes, without writing a formula.
The advantage over a spreadsheet is twofold. First, the calculation runs in an automated flow (webhook, CRM, project management tool) without manual intervention. Second, the time zone is managed at the scenario level, which a traditional spreadsheet does not handle natively for dates without a time component.
The convergence between Excel for the web and desktop Excel has also progressed: functions like NETWORKDAYS.INTL are now supported in Excel for the web, making project duration calculations directly usable in a browser, without a desktop version.
The choice of method depends on volume and context. For a one-off calculation, direct subtraction in a spreadsheet remains the shortest path. As soon as the calculation feeds into a recurring process (invoicing, project tracking, contract follow-up), automation via a no-code tool or a well-configured NETWORKDAYS formula avoids the silently accumulating copy-paste errors, month after month.