Difference Between Two Dates in Google Sheets: A Practical Guide
Learn how to calculate the difference between two dates in Google Sheets with raw day differences, DATEDIF breakdowns, and workday calculations. Step-by-step formulas, examples, and common pitfalls for students, professionals, and small business owners.
Goal: calculate the difference between two dates in Google Sheets. You can get a raw day count by subtracting dates (end_date - start_date), or use DATEDIF for years, months, and days. For workdays, use NETWORKDAYS. Decide whether you need total days, business days, or a breakdown by Y/M/D, and ensure both inputs are valid dates.
Why understanding date differences matters
In daily data tasks you frequently need to measure elapsed time, track milestones, or compute aging. The difference between two dates in google sheets is a foundational skill for project planning, invoicing, and reporting. When you compare start and end dates you can answer questions like how many days separate two events, whether a due date is still in range, or how long a project took in years, months, and days. This section walks you through practical methods, common pitfalls, and recommended formulas so you can compute accurate results quickly. You will learn to handle plain day counts, business days, and a breakdown by Y/M/D, so you can tailor the output to your exact goal.
Quick start: raw day difference (simple subtraction)
The simplest way to see how many days lie between two dates is to subtract the earlier date from the later date. In Google Sheets, if A2 is the start date and B2 is the end date, the formula end_date - start_date returns the number of days as a numeric value. This method assumes both inputs are valid dates; otherwise you may get errors or unexpected results. You can format the result as a number to keep it clean and usable in further calculations.
Getting a breakdown: using DATEDIF for years, months, days
For more granular results you can use the DATEDIF function to extract years, months, and days between two dates. The syntax is start_date, end_date, unit. Common units are Y for full years, M for full months, and D for total days. DATEDIF helps you build precise breakdowns like years and months, or months and days, depending on how you want to present the data. Note that DATEDIF is supported in Google Sheets but not always highlighted in menus, so you may type it directly.
Working with workdays: NETWORKDAYS and NETWORKDAYS.INTL
If your goal is to count only business days, NETWORKDAYS is the go-to function. It counts weekdays and excludes weekends by default, and you can also pass a list of holiday dates to skip. For more control over weekends, use NETWORKDAYS.INTL which lets you customize which days count as weekends. These functions are ideal for project timelines, payroll calculations, and service-level agreements.
Handling date formats and data quality
Date quality is critical for accurate differences. If a date is stored as text, subtraction or functions may fail or yield invalid results. Before calculating differences, ensure dates are recognized as actual dates. Use DATE, DATEVALUE, or VALUE to coerce text to dates when needed, and keep a consistent date format across the sheet. Avoid mixing time stamps with dates unless you explicitly want elapsed time down to hours and minutes.
Real-world examples: common scenarios
- Quick deadline check: find how many days remain until a due date. Use a simple subtraction with a future end date minus today. 2) Age calculation: determine how old someone is in years and months between birth date and today using DATEDIF. 3) Workday planning: count business days between project start and end, excluding weekends and holidays with NETWORKDAYS.
Common pitfalls and debugging tips
Common issues include swapping start and end dates, including time components, or relying on text dates. Ensure both inputs are real dates, not text, and consider time zones if your data includes time stamps. Always validate results with a few known samples and confirm that the formula covers the exact units you need (days, business days, or Y/M/D).
Advanced tips: combining with other functions
You can combine date difference results with IFERROR to handle invalid inputs, or with TEXT to format outputs for dashboards. Use ARRAYFORMULA to apply a date-difference calculation across entire columns, and LET to simplify complex expressions. These techniques help scale up calculations in larger datasets while keeping formulas readable.
Tools & Materials
- Google Sheets-ready spreadsheet(Contains two date columns to compare (e.g., Start_Date, End_Date))
- Google account access(Required to use Google Sheets online)
- Modern web browser(Chrome/Edge/Firefox recommended)
- Sample date data(ISO or recognizable date formats like 2025-12-31)
- Optional: holiday list(If calculating workdays, provide holidays as a range)
Steps
Estimated time: 15-25 minutes
- 1
Identify the date columns
Locate the start date column and the end date column in your sheet. Ensure both columns store actual dates (not text) to avoid conversion errors.
Tip: If dates are inconsistent, convert them with DATEVALUE or by re-entering a date in a standard format. - 2
Decide the desired output
Choose whether you want a raw day count, a workday count, or a breakdown by years, months, and days.
Tip: Write down your goal: simple elapsed time vs calendar vs business days to pick the right function. - 3
Enter the formula in a helper column
In a new column, enter the formula that matches your goal (subtraction for days, DATEDIF for Y/M/D, NETWORKDAYS for workdays).
Tip: Keep formulas in a single place first to verify correctness before copying across rows. - 4
Copy or fill down across rows
Drag the fill handle (or use ARRAYFORMULA for array-wide results) to apply the calculation to all rows.
Tip: Check a handful of rows to confirm pattern consistency and correct handling of empty cells. - 5
Validate results with sample data
Cross-check outputs against known date pairs to ensure units (days, months, years) align with expectations.
Tip: If results look off, inspect date formats and verify there are no stray time components. - 6
Optional: convert to static values
If you need a fixed result, copy the calculated column and paste as values.
Tip: Use Paste Special > Paste values only to keep the data intact without formula linkage.
FAQ
What is the difference between two dates in google sheets?
Date difference measures the elapsed time between two dates. Depending on the unit you choose, you can count days, months, or years. Start with simple subtraction for a day count and move to DATEDIF or NETWORKDAYS for more nuanced results.
Date difference measures elapsed time between two dates, using days, months, or years as your unit.
Which function should I use for years, months, and days?
For a breakdown by years, months, and days, use DATEDIF with appropriate units such as Y, M, or D. This gives flexibility to report complex elapsed times across a range of dates.
Use DATEDIF with units for years, months, and days to get a breakdown of elapsed time.
How do I count only workdays between two dates?
Use the NETWORKDAYS function to count workdays between two dates. You can add a list of holidays to exclude specific non-working days as well.
Use NETWORKDAYS to count workdays, with optional holidays for accuracy.
Can I include today in the calculation?
Yes. You can compare a past date to TODAY() to determine elapsed time up to the current moment. Combine TODAY with subtraction or DATEDIF as needed.
Yes, combine with TODAY to include the current date in your calculation.
What if the inputs are not real dates?
Non-date inputs can cause errors. Convert text to dates with DATEVALUE or VALUE, and validate formats before applying differences.
Convert any non-date text to dates before calculating differences.
Is ArrayFormula recommended for large datasets?
ArrayFormula lets you apply a calculation across entire columns without dragging formulas. It is helpful for large datasets but monitor performance with very large ranges.
Yes, use ArrayFormula to scale calculations across many rows.
Watch Video
The Essentials
- Use subtraction for quick day counts.
- Use DATEDIF for Y/M/D breakdowns.
- Use NETWORKDAYS for workdays between dates.
- Validate date inputs to avoid errors.
- Scale formulas with ArrayFormula and LET for large datasets.
