Google Sheets Date Difference: Mastering Date Calculations
Master google sheets date difference: calculate days, months, and years between dates using DATEDIF and NETWORKDAYS with practical examples, holidays, and best practices for accurate results.
Google Sheets measures the difference between two dates with DATEDIF (and NETWORKDAYS for workdays). For example, =DATEDIF(A2,B2,"D") returns full days between dates, while =DATEDIF(A2,B2,"M") gives complete months and =DATEDIF(A2,B2,"Y") full years. For business days, use NETWORKDAYS(A2,B2). If times are present, wrap dates with INT() to ignore time.
Introduction to google sheets date difference
According to How To Sheets, mastering the google sheets date difference starts with recognizing that dates are serial numbers and that the difference between two dates is simply the number of days between them, which can be expressed in days, months, or years. In this article, we will explore common patterns, show working formulas, and discuss how to handle times, holidays, and custom calendars. The goal is to give you practical, copy-paste-ready techniques suitable for students, professionals, and small business owners who rely on precise date calculations in Google Sheets. We will also highlight pitfalls and tips to keep your results trustworthy as you build dashboards and reports.
=DATEDIF(A2,B2,"D")=DATEDIF(A2,B2,"M")=DATEDIF(A2,B2,"Y")Inline notes:
- The first formula returns days between two dates.
- The second returns complete months, ignoring days within the last month.
- The third returns complete years.
Using DATEDIF for precise units (D, M, Y, MD, YM, YD)
DATEDIF accepts units: 'D' days, 'M' months, 'Y' years, 'MD' difference in days after removing months, 'YM' difference in months after removing years, 'YD' difference in days after removing years. Examples:
=DATEDIF(A2,B2,"MD")=DATEDIF(A2,B2,"YM")=DATEDIF(A2,B2,"YD")Notes:
- If date1 is after date2, DATEDIF returns an error unless you order dates with MIN/MAX.
- To guarantee a positive result, you can use:
=DATEDIF(MIN(A2,B2), MAX(A2,B2), "D")- You can also wrap the result in ABS for absolute value, but min/max is usually clearer.
Workdays and business calendars with NETWORKDAYS and NETWORKDAYS.INTL
For workday differences, NETWORKDAYS counts weekdays between two dates, optionally excluding holidays:
=NETWORKDAYS(A2,B2)=NETWORKDAYS.INTL(A2,B2,"0000011")If you have a holiday list (Holidays!$A$2:$A$20), pass it as a third argument to exclude those dates:
=NETWORKDAYS(A2,B2,Holidays!$A$2:$A$20)Tip:
- NETWORKDAYS.INTL lets you customize weekends using a 7-digit pattern (Mon-Sun). This is useful for nonstandard workweeks or international calendars.
- For nonzero holidays and weekends, combine with IFERROR to avoid miscounts.
Practical examples with real data and mixed date formats
Consider a small dataset where column A contains Start Dates and column B contains End Dates. Ensure dates are recognized by Sheets or convert with DATEVALUE if needed. Example results from a simple table:
# A2 and B2 are dates
# Difference in days
=C2: =DATEDIF(A2,B2,"D")
# Difference in months (full months)
=D2: =DATEDIF(A2,B2,"M")
# Difference in years (full years)
=E2: =DATEDIF(A2,B2,"Y")Note how dropping the time portion changes results:
# If A2 or B2 contain times, strip with INT
=DATEDIF(INT(A2), INT(B2), "D")Common variations include combining DATEDIF with IF statements to handle negative results or missing dates.
Troubleshooting and best practices
- Always confirm that both dates are true date values. If a cell looks like a date but is stored as text, convert with DATEVALUE.
- Use MIN(A2,B2) and MAX(A2,B2) when you need a guaranteed nonnegative difference regardless of date order.
- For business calendars, prefer NETWORKDAYS.INTL with a weekend pattern and an optional holidays range.
- When using time stamps, wrap with INT to ignore the time portion, or use the DATEDIF unit options (YD, MD, YM) to isolate components.
- Validate results with a quick sanity check: for two consecutive days, the day difference should be 1 (or 0 if same day).
The How To Sheets team recommends validating date-difference formulas against a few known cases to build confidence before applying them to dashboards.
Steps
Estimated time: 15-30 minutes
- 1
Prepare date columns
Create two date columns (A for start, B for end) and enter sample dates. Ensure they are recognized as dates by Sheets (YYYY-MM-DD or Date picker).
Tip: Label columns and set a test row to verify calculations. - 2
Choose the difference unit
Decide whether you need days, months, or years. DATEDIF supports D, M, Y, plus MD/YM/YD variants for nuanced components.
Tip: If you plan dashboards, start with days and then derive months/years as needed. - 3
Enter the DATEDIF formula
In a new column, enter =DATEDIF(A2,B2,"D") for days, and adjust the unit to D, M, or Y as required. Drag the fill handle to apply to more rows.
Tip: Double-check quoted units; incorrect units return #VALUE! errors. - 4
Handle times and holidays
If times appear in dates, wrap with INT. For business days, use NETWORKDAYS with an optional holidays range.
Tip: Keep a named range for holidays to reuse across sheets. - 5
Validate and summarize
Test a few edge cases (same date, reverse order, holidays). Build a small summary table if needed to monitor date differences across rows.
Tip: Cross-check with a quick manual calculation to ensure accuracy.
Prerequisites
Required
- Required
- Required
- Internet accessRequired
Optional
- Holidays list (optional)Optional
- Sample dataset with two date columnsOptional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| CopyCopy selected cell or formula | Ctrl+C |
| PastePaste into target cell | Ctrl+V |
| Fill downCopy formula or value down a column | Ctrl+D |
| UndoRevert recent change | Ctrl+Z |
| SaveSave current sheet | Ctrl+S |
FAQ
What is the best unit for daily date differences?
Use days (D) for daily counts; for months or years, choose M or Y respectively. For nuanced components, explore YM, YD, or MD. Always verify whether the order of dates affects the result.
For daily gaps, use D; for longer spans, use M or Y and validate with edge cases.
Can DATEDIF handle negative differences?
DATEDIF expects date1 to be earlier than date2. If not, swap the dates or use MIN/MAX to order them before calculating.
If you need always-positive results, sort the dates first or use a MIN/MAX approach.
How do I count holidays?
Pass a holidays range to NETWORKDAYS or NETWORKDAYS.INTL to exclude those dates from the workday count.
Include a holidays list in NETWORKDAYS to get accurate business-day gaps.
What if there are times in the date values?
Strip time with INT() before calculating, or use DATEDIF with the D unit after converting to dates.
Remove time parts to avoid skewed results, then compute the difference.
Is there a formula to always get a positive difference?
Yes. Use MIN/MAX to order dates, or wrap with ABS if appropriate. The clear approach is to order first:
Order the dates with MIN and MAX to ensure nonnegative results.
The Essentials
- Choose the correct DATEDIF unit for your needs
- Use NETWORKDAYS for workday differences
- Convert text dates to real dates before calculations
- Validate edge cases to ensure accuracy