DATEDIF and date differences
Updated · Works in: all current Excel versions, Google Sheets · Every example checked before release
Short answer
=DATEDIF(start, end, "Y") gives completed years ("M" months, "D" days), start first. YEARFRAC(start, end, 1) gives years with decimals, NETWORKDAYS counts working days, and MOD(end-start,1)*24 gives hours, even past midnight.
Dates are stored as day numbers (in Excel, 1 January 1900 is day 1), so subtracting gives days. For whole years and months, working days or hours, use the functions below.
Build and take apart dates
DATE(year, month, day) builds a date, and months past 12 roll into the next year. MONTH(date) gives 1 to 12. EDATE(date, months) moves a date by whole months and EOMONTH(date, months) jumps to a month's last day: EOMONTH(F2, 1) is the end of next month.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Invoice | Year | Month | Day | Amount | Date |
| 2 | INV-301 | 2026 | 1 | 28 | $1,250.00 | 28 Jan 2026 |
| 3 | INV-302 | 2026 | 2 | 3 | $480.00 | 3 Feb 2026 |
| 4 | INV-303 | 2026 | 2 | 17 | $2,200.00 | 17 Feb 2026 |
| 5 | INV-304 | 2026 | 3 | 9 | $975.00 | 9 Mar 2026 |
| 6 | INV-305 | 2026 | 3 | 31 | $640.00 | 31 Mar 2026 |
| 7 | INV-306 | 2026 | 4 | 14 | $1,830.00 | 14 Apr 2026 |
Whole years with DATEDIF
DATEDIF(start, end, "Y") counts completed years; use "M" for months and "D" for days. The start must come first, or you get #NUM!. Excel keeps DATEDIF for compatibility with old Lotus 1-2-3 files, and Microsoft advises against its "MD" unit, which can give negative or wrong results.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Name | Dept | Started | Born | Salary | Level | Years here | As of |
| 2 | Amara Osei | Sales | 15 Apr 2019 | 2 Aug 1990 | $58,000 | 2 | 7 | 30 Jun 2026 |
| 3 | Ben Fischer | Support | 9 Jan 2023 | 21 Nov 1996 | $41,000 | 1 | 3 | |
| 4 | Lina Chen | Engineering | 1 Sep 2016 | 14 Mar 1987 | $88,000 | 4 | 9 | |
| 5 | Diego Alvarez | Sales | 28 Jun 2021 | 30 Jun 1993 | $52,000 | 2 | 5 | |
| 6 | Eva Novak | Finance | 12 Feb 2018 | 5 Dec 1985 | $74,000 | 3 | 8 | |
| 7 | Farid Haddad | Engineering | 4 Mar 2024 | 19 Jan 1998 | $68,000 | 2 | 2 | |
| 8 | Grace Kim | Support | 19 Oct 2020 | 27 Sep 1991 | $45,000 | 2 | 5 | |
| 9 | Hugo Martin | Finance | 11 May 2015 | 8 Apr 1979 | $88,000 | 4 | 11 | |
| 10 | Ines Varga | Engineering | 14 Nov 2022 | 28 Feb 1995 | $79,000 | 3 | 3 | |
| 11 | Kenji Mori | Sales | 25 Aug 2025 | 16 Jul 2000 | $39,000 | 1 | 0 |
Years with decimals: YEARFRAC
YEARFRAC(start, end, 1) gives fractional years using actual days. The basis argument picks the day-count convention (0, the default, is US 30/360).
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Name | Dept | Started | Born | Salary | Level | Tenure | As of |
| 2 | Amara Osei | Sales | 15 Apr 2019 | 2 Aug 1990 | $58,000 | 2 | 7.21 | 30 Jun 2026 |
| 3 | Ben Fischer | Support | 9 Jan 2023 | 21 Nov 1996 | $41,000 | 1 | 3.47 | |
| 4 | Lina Chen | Engineering | 1 Sep 2016 | 14 Mar 1987 | $88,000 | 4 | 9.83 | |
| 5 | Diego Alvarez | Sales | 28 Jun 2021 | 30 Jun 1993 | $52,000 | 2 | 5.01 | |
| 6 | Eva Novak | Finance | 12 Feb 2018 | 5 Dec 1985 | $74,000 | 3 | 8.38 | |
| 7 | Farid Haddad | Engineering | 4 Mar 2024 | 19 Jan 1998 | $68,000 | 2 | 2.32 | |
| 8 | Grace Kim | Support | 19 Oct 2020 | 27 Sep 1991 | $45,000 | 2 | 5.69 | |
| 9 | Hugo Martin | Finance | 11 May 2015 | 8 Apr 1979 | $88,000 | 4 | 11.14 | |
| 10 | Ines Varga | Engineering | 14 Nov 2022 | 28 Feb 1995 | $79,000 | 3 | 3.63 | |
| 11 | Kenji Mori | Sales | 25 Aug 2025 | 16 Jul 2000 | $39,000 | 1 | 0.85 |
Working days
NETWORKDAYS(start, end, holidays) counts weekdays between two dates, both ends included; WORKDAY(start, days, holidays) goes forward a number of working days. For other weekends, NETWORKDAYS.INTL(start, end, 7, holidays) takes a weekend code: 1 is Saturday–Sunday, 7 Friday–Saturday, 11 Sunday only. WEEKDAY(date, 2) numbers the days Monday = 1 to Sunday = 7, and ISOWEEKNUM(date) gives the ISO 8601 week.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Task | Owner | Start | Due | Hours | Work days | Holidays | |
| 2 | Kickoff workshop | Mara | 2 Mar 2026 | 4 Mar 2026 | 6 | 3 | 3 Apr 2026 | |
| 3 | Content audit | Jonas | 5 Mar 2026 | 20 Mar 2026 | 24 | 12 | 6 Apr 2026 | |
| 4 | Wireframes | Priya | 16 Mar 2026 | 2 Apr 2026 | 40 | 14 | 1 May 2026 | |
| 5 | Visual design | Elif | 30 Mar 2026 | 17 Apr 2026 | 56 | 13 | ||
| 6 | Copywriting | Tomás | 1 Apr 2026 | 24 Apr 2026 | 32 | 16 | ||
| 7 | Build | Kenji | 13 Apr 2026 | 15 May 2026 | 120 | 24 | ||
| 8 | Testing | Sofia | 11 May 2026 | 22 May 2026 | 30 | 10 | ||
| 9 | Launch | Leo | 26 May 2026 | 27 May 2026 | 8 | 2 |
Hours between two times
Times are fractions of a day, so multiply the difference by 24. For shifts past midnight, MOD(end - start, 1) wraps around.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Name | Start | End | Break | Hours |
| 2 | Mon 2 Mar | Mara | 9:00 | 17:30 | 30 min | 8.50 |
| 3 | Mon 2 Mar | Jonas | 8:15 | 16:45 | 45 min | 8.50 |
| 4 | Tue 3 Mar | Mara | 9:30 | 18:00 | 30 min | 8.50 |
| 5 | Tue 3 Mar | Priya | 22:00 | 6:00 | 30 min | 8.00 |
| 6 | Wed 4 Mar | Jonas | 7:45 | 15:15 | 30 min | 7.50 |
| 7 | Wed 4 Mar | Priya | 21:30 | 5:30 | 45 min | 8.00 |
| 8 | Thu 5 Mar | Mara | 10:00 | 19:15 | 60 min | 9.25 |
| 9 | Fri 6 Mar | Jonas | 8:00 | 12:30 | 0 min | 4.50 |
Check yourself
One real exercise from the “Dates and time maths” unit. Checked on our server; no account needed.Work out each person's age in full years: from their birthday (Born, D2) to the As of date in H2. Start in G2.
- Use DATEDIF
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Name | Dept | Started | Born | Salary | Level | Age | As of |
| 2 | Amara Osei | Sales | 15 Apr 2019 | 2 Aug 1990 | $58,000 | 2 | 30 Jun 2026 | |
| 3 | Ben Fischer | Support | 9 Jan 2023 | 21 Nov 1996 | $41,000 | 1 | ||
| 4 | Lina Chen | Engineering | 1 Sep 2016 | 14 Mar 1987 | $88,000 | 4 | ||
| 5 | Diego Alvarez | Sales | 28 Jun 2021 | 30 Jun 1993 | $52,000 | 2 | ||
| 6 | Eva Novak | Finance | 12 Feb 2018 | 5 Dec 1985 | $74,000 | 3 | ||
| 7 | Farid Haddad | Engineering | 4 Mar 2024 | 19 Jan 1998 | $68,000 | 2 | ||
| 8 | Grace Kim | Support | 19 Oct 2020 | 27 Sep 1991 | $45,000 | 2 | ||
| 9 | Hugo Martin | Finance | 11 May 2015 | 8 Apr 1979 | $88,000 | 4 | ||
| 10 | Ines Varga | Engineering | 14 Nov 2022 | 28 Feb 1995 | $79,000 | 3 | ||
| 11 | Kenji Mori | Sales | 25 Aug 2025 | 16 Jul 2000 | $39,000 | 1 |
Common mistakes
Putting the later date first in DATEDIF.
Dividing days by 365 for age: leap years make it drift. Use DATEDIF or YEARFRAC.
Using TODAY() in a report that must give the same answer tomorrow. Put the report date in a cell.
Questions
Does DATEDIF exist in Google Sheets?+
Yes, with the same units ("Y", "M", "D", "YM", "MD", "YD"). In Excel, avoid "MD": Microsoft documents that it can be inaccurate.
How do I add months to a date?+
EDATE(date, months). EOMONTH(date, months) jumps to the end of the month instead.
Reading is step one. Now make it stick.
Practise it in a five-minute lesson on a live sheet, with instant feedback. No account needed for the free units.