formulastreak

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.

Freelance invoices · edit F2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultF2 = 28 Jan 2026
ABCDEF
1InvoiceYearMonthDayAmountDate
2INV-3012026128$1,250.0028 Jan 2026
3INV-302202623$480.003 Feb 2026
4INV-3032026217$2,200.0017 Feb 2026
5INV-304202639$975.009 Mar 2026
6INV-3052026331$640.0031 Mar 2026
7INV-3062026414$1,830.0014 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.

Team directory · edit G2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultG2 = 7
ABCDEFGH
1NameDeptStartedBornSalaryLevelYears hereAs of
2Amara OseiSales15 Apr 20192 Aug 1990$58,0002730 Jun 2026
3Ben FischerSupport9 Jan 202321 Nov 1996$41,00013
4Lina ChenEngineering1 Sep 201614 Mar 1987$88,00049
5Diego AlvarezSales28 Jun 202130 Jun 1993$52,00025
6Eva NovakFinance12 Feb 20185 Dec 1985$74,00038
7Farid HaddadEngineering4 Mar 202419 Jan 1998$68,00022
8Grace KimSupport19 Oct 202027 Sep 1991$45,00025
9Hugo MartinFinance11 May 20158 Apr 1979$88,000411
10Ines VargaEngineering14 Nov 202228 Feb 1995$79,00033
11Kenji MoriSales25 Aug 202516 Jul 2000$39,00010

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).

Team directory · edit G2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultG2 = 7.21
ABCDEFGH
1NameDeptStartedBornSalaryLevelTenureAs of
2Amara OseiSales15 Apr 20192 Aug 1990$58,00027.2130 Jun 2026
3Ben FischerSupport9 Jan 202321 Nov 1996$41,00013.47
4Lina ChenEngineering1 Sep 201614 Mar 1987$88,00049.83
5Diego AlvarezSales28 Jun 202130 Jun 1993$52,00025.01
6Eva NovakFinance12 Feb 20185 Dec 1985$74,00038.38
7Farid HaddadEngineering4 Mar 202419 Jan 1998$68,00022.32
8Grace KimSupport19 Oct 202027 Sep 1991$45,00025.69
9Hugo MartinFinance11 May 20158 Apr 1979$88,000411.14
10Ines VargaEngineering14 Nov 202228 Feb 1995$79,00033.63
11Kenji MoriSales25 Aug 202516 Jul 2000$39,00010.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.

Website relaunch plan · edit F2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultF2 = 3
ABCDEFGH
1TaskOwnerStartDueHoursWork daysHolidays
2Kickoff workshopMara2 Mar 20264 Mar 2026633 Apr 2026
3Content auditJonas5 Mar 202620 Mar 202624126 Apr 2026
4WireframesPriya16 Mar 20262 Apr 202640141 May 2026
5Visual designElif30 Mar 202617 Apr 20265613
6CopywritingTomás1 Apr 202624 Apr 20263216
7BuildKenji13 Apr 202615 May 202612024
8TestingSofia11 May 202622 May 20263010
9LaunchLeo26 May 202627 May 202682

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.

Weekly timesheet · edit F2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultF2 = 8.50
ABCDEF
1DateNameStartEndBreakHours
2Mon 2 MarMara9:0017:3030 min8.50
3Mon 2 MarJonas8:1516:4545 min8.50
4Tue 3 MarMara9:3018:0030 min8.50
5Tue 3 MarPriya22:006:0030 min8.00
6Wed 4 MarJonas7:4515:1530 min7.50
7Wed 4 MarPriya21:305:3045 min8.00
8Thu 5 MarMara10:0019:1560 min9.25
9Fri 6 MarJonas8:0012:300 min4.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
Select G2 and type = to start its formula (in the cell or the formula bar)
ABCDEFGH
1NameDeptStartedBornSalaryLevelAgeAs of
2Amara OseiSales15 Apr 20192 Aug 1990$58,000230 Jun 2026
3Ben FischerSupport9 Jan 202321 Nov 1996$41,0001
4Lina ChenEngineering1 Sep 201614 Mar 1987$88,0004
5Diego AlvarezSales28 Jun 202130 Jun 1993$52,0002
6Eva NovakFinance12 Feb 20185 Dec 1985$74,0003
7Farid HaddadEngineering4 Mar 202419 Jan 1998$68,0002
8Grace KimSupport19 Oct 202027 Sep 1991$45,0002
9Hugo MartinFinance11 May 20158 Apr 1979$88,0004
10Ines VargaEngineering14 Nov 202228 Feb 1995$79,0003
11Kenji MoriSales25 Aug 202516 Jul 2000$39,0001

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.

Practise it now