DATEDIF

Date & Timeintermediate

DATEDIF returns the interval between two dates in your chosen unit. Despite being one of the most useful date formulas, it's undocumented in Excel's formula builder (a legacy from Lotus 1-2-3 compatibility). It works perfectly. Google Sheets documents it fully.

Syntax

Excel:=DATEDIF(start_date, end_date, unit)
Google Sheets: =DATEDIF(start_date, end_date, unit)

Parameters

start_dateThe earlier date.
end_dateThe later date (must be >= start_date).
unit"Y" = complete years, "M" = complete months, "D" = days, "MD" = days ignoring months/years, "YM" = months ignoring years, "YD" = days ignoring years.

? = optional parameter

Worked Example

How many days between invoice date Jan 15, 2025 and payment date Feb 1, 2025?

Row numbersDH
Header rowinvoice_datepayment_datedays_to_pay
22025-01-152025-02-0117

Formula

=DATEDIF(A2, B2, "D")

Result

17

DATEDIF with 'D' measures the total number of days between A2 and B2. In this sample, the span from 2025-01-15 to 2025-02-01 is 17 days.

Practice Exercises

2 exercises - work through each one using the data table below

Exercise 1 of 2beginner
#DATEDIF-001

Question

How many days did it take to pay invoice INV-1001? (Invoice date: Jan 15, 2025. Payment date: Feb 1, 2025.)

Dataset

Vendor Spend Tracker (150 rows total)

Download CSV
Row numbersABCD
Header row
2INV-10012025-01-152025-02-01Paid
3INV-10022025-01-182025-01-25Paid
4INV-10032025-01-222025-02-10Paid
5INV-10042025-02-032025-02-15Paid
6INV-10052025-02-082025-02-20Paid
7INV-10062025-02-14--Pending
8INV-10072025-02-192025-03-01Paid
9INV-10082025-03-05--Overdue
10INV-10092025-03-102025-03-22Paid
11INV-10102025-03-152025-03-28Paid
12INV-10112025-01-042025-01-18Paid
13INV-10122025-01-092025-02-01Paid
14INV-10132025-01-102025-01-19Paid
15INV-10142025-01-162025-02-01Paid
16INV-10152025-01-172025-01-29Paid
17INV-10162025-01-212025-01-29Paid
18INV-10172025-02-092025-02-18Paid
19INV-10182025-02-182025-02-28Paid
20INV-10192025-02-192025-03-04Paid
21INV-10202025-02-222025-03-19Paid
22INV-10212025-02-252025-03-17Paid
23INV-10222025-03-02--Pending
24INV-10232025-03-042025-03-11Paid
25INV-10242025-03-102025-03-19Paid
26INV-10252025-03-122025-03-22Paid
27INV-10262025-03-132025-03-31Paid
28INV-10272025-03-142025-03-30Paid
29INV-10282025-03-142025-04-06Paid
30INV-10292025-03-152025-04-03Paid
31INV-10302025-03-172025-04-10Paid
32INV-10312025-03-192025-03-28Paid
33INV-10322025-03-202025-03-31Paid
34INV-10332025-04-01--Pending
35INV-10342025-04-012025-04-23Paid
36INV-10352025-04-032025-04-27Paid
37INV-10362025-04-052025-04-24Paid
38INV-10372025-04-072025-04-15Paid
39INV-10382025-04-102025-04-20Paid
40INV-10392025-04-112025-04-30Paid
41INV-10402025-04-152025-04-26Paid
42INV-10412025-04-15--Overdue
43INV-10422025-04-162025-05-10Paid
44INV-10432025-04-172025-05-07Paid
45INV-10442025-04-202025-05-05Paid
46INV-10452025-04-222025-05-10Paid
47INV-10462025-04-232025-05-04Paid
48INV-10472025-04-242025-04-29Paid
49INV-10482025-05-012025-05-10Paid
50INV-10492025-05-032025-05-17Paid
51INV-10502025-05-062025-05-30Paid
52INV-10512025-05-15--Overdue
53INV-10522025-05-172025-05-31Paid
54INV-10532025-05-182025-05-30Paid
55INV-10542025-05-182025-05-30Paid
56INV-10552025-05-212025-06-03Paid
57INV-10562025-05-242025-06-05Paid
58INV-10572025-06-012025-06-18Paid
59INV-10582025-06-062025-06-27Paid
60INV-10592025-06-072025-06-23Paid
61INV-10602025-06-102025-06-24Paid
62INV-10612025-06-102025-06-17Paid
63INV-10622025-06-122025-06-22Paid
64INV-10632025-06-13--Overdue
65INV-10642025-06-13--Pending
66INV-10652025-06-152025-07-01Paid
67INV-10662025-06-162025-07-07Paid
68INV-10672025-06-172025-06-26Paid
69INV-10682025-06-232025-07-17Paid
70INV-10692025-06-252025-07-06Paid
71INV-10702025-07-012025-07-11Paid
72INV-10712025-07-062025-07-22Paid
73INV-10722025-07-07--Overdue
74INV-10732025-07-082025-07-22Paid
75INV-10742025-07-082025-07-20Paid
76INV-10752025-07-082025-07-31Paid
77INV-10762025-07-092025-07-22Paid
78INV-10772025-07-112025-07-21Paid
79INV-10782025-07-112025-07-19Paid
80INV-10792025-07-132025-07-20Paid
81INV-10802025-07-152025-07-24Paid
82INV-10812025-07-162025-08-05Paid
83INV-10822025-07-172025-07-28Paid
84INV-10832025-07-182025-07-30Paid
85INV-10842025-07-192025-07-26Paid
86INV-10852025-07-212025-08-06Paid
87INV-10862025-07-232025-08-09Paid
88INV-10872025-08-042025-08-17Paid
89INV-10882025-08-042025-08-16Paid
90INV-10892025-08-052025-08-20Paid
91INV-10902025-08-052025-08-21Paid
92INV-10912025-08-052025-08-24Paid
93INV-10922025-08-072025-08-27Paid
94INV-10932025-08-082025-08-23Paid
95INV-10942025-08-092025-08-23Paid
96INV-10952025-08-122025-08-31Paid
97INV-10962025-08-132025-08-26Paid
98INV-10972025-08-132025-09-06Paid
99INV-10982025-08-152025-08-29Paid
100INV-10992025-08-152025-09-08Paid
101INV-11002025-08-16--Pending
102INV-11012025-08-172025-08-27Paid
103INV-11022025-08-172025-08-28Paid
104INV-11032025-08-172025-08-27Paid
105INV-11042025-08-182025-09-12Paid
106INV-11052025-08-222025-08-31Paid
107INV-11062025-08-24--Overdue
108INV-11072025-08-24--Pending
109INV-11082025-09-042025-09-17Paid
110INV-11092025-09-082025-09-30Paid
111INV-11102025-09-082025-09-22Paid
112INV-11112025-09-132025-10-03Paid
113INV-11122025-09-16--Pending
114INV-11132025-09-17--Pending
115INV-11142025-09-182025-10-11Paid
116INV-11152025-09-192025-09-30Paid
117INV-11162025-09-192025-10-01Paid
118INV-11172025-09-192025-09-28Paid
119INV-11182025-09-202025-10-15Paid
120INV-11192025-09-212025-10-06Paid
121INV-11202025-09-212025-10-09Paid
122INV-11212025-09-212025-10-03Paid
123INV-11222025-09-242025-10-18Paid
124INV-11232025-09-25--Overdue
125INV-11242025-09-252025-10-16Paid
126INV-11252025-09-252025-10-12Paid
127INV-11262025-09-252025-10-10Paid
128INV-11272025-10-03--Overdue
129INV-11282025-10-042025-10-17Paid
130INV-11292025-10-052025-10-22Paid
131INV-11302025-10-052025-10-23Paid
132INV-11312025-10-062025-10-23Paid
133INV-11322025-10-102025-10-26Paid
134INV-11332025-10-112025-10-18Paid
135INV-11342025-10-122025-10-30Paid
136INV-11352025-10-212025-10-29Paid
137INV-11362025-10-242025-11-07Paid
138INV-11372025-10-252025-11-12Paid
139INV-11382025-11-072025-11-20Paid
140INV-11392025-11-132025-11-29Paid
141INV-11402025-11-202025-12-09Paid
142INV-11412025-11-212025-12-06Paid
143INV-11422025-11-242025-12-05Paid
144INV-11432025-12-092026-01-03Paid
145INV-11442025-12-152026-01-05Paid
146INV-11452025-12-162025-12-25Paid
147INV-11462025-12-182026-01-05Paid
148INV-11472025-12-192026-01-12Paid
149INV-11482025-12-192025-12-27Paid
150INV-11492025-12-202026-01-13Paid
151INV-11502025-12-23--Pending
152

Scroll to explore the table, then sort or filter by column as needed.

Hints

Your Answer

Exercise 2 of 2intermediate
#DATEDIF-002

Question

What is the average number of days between invoice_date and payment_date for all Paid invoices? Round to the nearest whole number.

Dataset

Vendor Spend Tracker (150 rows total)

Download CSV
Row numbersABCD
Header row
2INV-10012025-01-152025-02-01Paid
3INV-10022025-01-182025-01-25Paid
4INV-10032025-01-222025-02-10Paid
5INV-10042025-02-032025-02-15Paid
6INV-10052025-02-082025-02-20Paid
7INV-10062025-02-14--Pending
8INV-10072025-02-192025-03-01Paid
9INV-10082025-03-05--Overdue
10INV-10092025-03-102025-03-22Paid
11INV-10102025-03-152025-03-28Paid
12INV-10112025-01-042025-01-18Paid
13INV-10122025-01-092025-02-01Paid
14INV-10132025-01-102025-01-19Paid
15INV-10142025-01-162025-02-01Paid
16INV-10152025-01-172025-01-29Paid
17INV-10162025-01-212025-01-29Paid
18INV-10172025-02-092025-02-18Paid
19INV-10182025-02-182025-02-28Paid
20INV-10192025-02-192025-03-04Paid
21INV-10202025-02-222025-03-19Paid
22INV-10212025-02-252025-03-17Paid
23INV-10222025-03-02--Pending
24INV-10232025-03-042025-03-11Paid
25INV-10242025-03-102025-03-19Paid
26INV-10252025-03-122025-03-22Paid
27INV-10262025-03-132025-03-31Paid
28INV-10272025-03-142025-03-30Paid
29INV-10282025-03-142025-04-06Paid
30INV-10292025-03-152025-04-03Paid
31INV-10302025-03-172025-04-10Paid
32INV-10312025-03-192025-03-28Paid
33INV-10322025-03-202025-03-31Paid
34INV-10332025-04-01--Pending
35INV-10342025-04-012025-04-23Paid
36INV-10352025-04-032025-04-27Paid
37INV-10362025-04-052025-04-24Paid
38INV-10372025-04-072025-04-15Paid
39INV-10382025-04-102025-04-20Paid
40INV-10392025-04-112025-04-30Paid
41INV-10402025-04-152025-04-26Paid
42INV-10412025-04-15--Overdue
43INV-10422025-04-162025-05-10Paid
44INV-10432025-04-172025-05-07Paid
45INV-10442025-04-202025-05-05Paid
46INV-10452025-04-222025-05-10Paid
47INV-10462025-04-232025-05-04Paid
48INV-10472025-04-242025-04-29Paid
49INV-10482025-05-012025-05-10Paid
50INV-10492025-05-032025-05-17Paid
51INV-10502025-05-062025-05-30Paid
52INV-10512025-05-15--Overdue
53INV-10522025-05-172025-05-31Paid
54INV-10532025-05-182025-05-30Paid
55INV-10542025-05-182025-05-30Paid
56INV-10552025-05-212025-06-03Paid
57INV-10562025-05-242025-06-05Paid
58INV-10572025-06-012025-06-18Paid
59INV-10582025-06-062025-06-27Paid
60INV-10592025-06-072025-06-23Paid
61INV-10602025-06-102025-06-24Paid
62INV-10612025-06-102025-06-17Paid
63INV-10622025-06-122025-06-22Paid
64INV-10632025-06-13--Overdue
65INV-10642025-06-13--Pending
66INV-10652025-06-152025-07-01Paid
67INV-10662025-06-162025-07-07Paid
68INV-10672025-06-172025-06-26Paid
69INV-10682025-06-232025-07-17Paid
70INV-10692025-06-252025-07-06Paid
71INV-10702025-07-012025-07-11Paid
72INV-10712025-07-062025-07-22Paid
73INV-10722025-07-07--Overdue
74INV-10732025-07-082025-07-22Paid
75INV-10742025-07-082025-07-20Paid
76INV-10752025-07-082025-07-31Paid
77INV-10762025-07-092025-07-22Paid
78INV-10772025-07-112025-07-21Paid
79INV-10782025-07-112025-07-19Paid
80INV-10792025-07-132025-07-20Paid
81INV-10802025-07-152025-07-24Paid
82INV-10812025-07-162025-08-05Paid
83INV-10822025-07-172025-07-28Paid
84INV-10832025-07-182025-07-30Paid
85INV-10842025-07-192025-07-26Paid
86INV-10852025-07-212025-08-06Paid
87INV-10862025-07-232025-08-09Paid
88INV-10872025-08-042025-08-17Paid
89INV-10882025-08-042025-08-16Paid
90INV-10892025-08-052025-08-20Paid
91INV-10902025-08-052025-08-21Paid
92INV-10912025-08-052025-08-24Paid
93INV-10922025-08-072025-08-27Paid
94INV-10932025-08-082025-08-23Paid
95INV-10942025-08-092025-08-23Paid
96INV-10952025-08-122025-08-31Paid
97INV-10962025-08-132025-08-26Paid
98INV-10972025-08-132025-09-06Paid
99INV-10982025-08-152025-08-29Paid
100INV-10992025-08-152025-09-08Paid
101INV-11002025-08-16--Pending
102INV-11012025-08-172025-08-27Paid
103INV-11022025-08-172025-08-28Paid
104INV-11032025-08-172025-08-27Paid
105INV-11042025-08-182025-09-12Paid
106INV-11052025-08-222025-08-31Paid
107INV-11062025-08-24--Overdue
108INV-11072025-08-24--Pending
109INV-11082025-09-042025-09-17Paid
110INV-11092025-09-082025-09-30Paid
111INV-11102025-09-082025-09-22Paid
112INV-11112025-09-132025-10-03Paid
113INV-11122025-09-16--Pending
114INV-11132025-09-17--Pending
115INV-11142025-09-182025-10-11Paid
116INV-11152025-09-192025-09-30Paid
117INV-11162025-09-192025-10-01Paid
118INV-11172025-09-192025-09-28Paid
119INV-11182025-09-202025-10-15Paid
120INV-11192025-09-212025-10-06Paid
121INV-11202025-09-212025-10-09Paid
122INV-11212025-09-212025-10-03Paid
123INV-11222025-09-242025-10-18Paid
124INV-11232025-09-25--Overdue
125INV-11242025-09-252025-10-16Paid
126INV-11252025-09-252025-10-12Paid
127INV-11262025-09-252025-10-10Paid
128INV-11272025-10-03--Overdue
129INV-11282025-10-042025-10-17Paid
130INV-11292025-10-052025-10-22Paid
131INV-11302025-10-052025-10-23Paid
132INV-11312025-10-062025-10-23Paid
133INV-11322025-10-102025-10-26Paid
134INV-11332025-10-112025-10-18Paid
135INV-11342025-10-122025-10-30Paid
136INV-11352025-10-212025-10-29Paid
137INV-11362025-10-242025-11-07Paid
138INV-11372025-10-252025-11-12Paid
139INV-11382025-11-072025-11-20Paid
140INV-11392025-11-132025-11-29Paid
141INV-11402025-11-202025-12-09Paid
142INV-11412025-11-212025-12-06Paid
143INV-11422025-11-242025-12-05Paid
144INV-11432025-12-092026-01-03Paid
145INV-11442025-12-152026-01-05Paid
146INV-11452025-12-162025-12-25Paid
147INV-11462025-12-182026-01-05Paid
148INV-11472025-12-192026-01-12Paid
149INV-11482025-12-192025-12-27Paid
150INV-11492025-12-202026-01-13Paid
151INV-11502025-12-23--Pending
152

Scroll to explore the table, then sort or filter by column as needed.

Hints

Your Answer

Want more practice?

Test Me mode randomizes exercises across all formulas without telling you which formula to use.

Try Test Me ->