Calculate the Difference Between Two Dates in Excel
Excel DATEDIF Function
Note: If you are experienced using Excel functions try the Excel DATEDIF Function Text-only Tutorial which is a bare bones tutorial on the Date Difference function.
This tutorial is intended for users new to using Excel's date functions.
Calculate the Number of Days Between Two Dates in Excel
Excel has several built in date functions that can be used to calculate the number of days between two dates.
Each date function does a different job so that the results differ from one function to the next. Which one you use, therefore, depends on the results you want.
Excel Date Difference Function
The DATEDIF function can be used to calculate the time period or difference between two dates. This time period can be calculated as the number of days, months, or years between the two dates.
Uses for this function include planning or writing proposals to determine the time frame for an upcoming project. It can also be used, along with their birth date, to calculate a person's age in years, months, and days.
DATEDIF Function Undocumented
An interesting point about DATEDIF is that it is an "undocumented" function which means it is not listed with other Date functions under the formula tab in Excel.
To use DATEDIF you must type it manually into a cell on the worksheet rather than using the dialog box method available for other functions.
The DATEDIF Function's Syntax and Arguments
A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.
The syntax for the DATEDIF function is:
= DATEDIF ( start_date , end_date , "interval")
The function has three arguments that need to be entered as part of the function:
Start_date - (required) the start date of the chosen time period. The actual start date can be entered for this argument or the cell reference to the location of this data in the worksheet can be entered instead.
End_date - (required) the end date of the chosen time period. As with the Start_date, enter the actual end date or the cell reference to the location of this data in the worksheet.
"Interval" - (required) tells the function to find the number of days ("D"), complete months ("M"), or complete years ("Y") between the two dates.
Note: When entering the Interval argument you must include the quotation marks such as "D".
More on the Interval Argument
The Interval argument can also contain a combination of days, months, and years in order to increase the variety of answers returned by the function.
For help with this example, see the image above.
Since the DATEDIF function can be used to find either the number of days, the number of months, or the number of years between two dates, this example will show separately how to calculate all three between the dates of July 9, 2010 and November 2, 2012.
Note: To avoid calculation problems that can occur if dates are accidentally entered as text, the DATE function will be used to enter the dates used in the function.
Also Note: When manually entering a function in Excel you must type a comma between each argument to act as a separator.
Entering the Data
Calculating the Difference in Days
DATEDIF returns only the number of full or complete months between the two dates.
DATEDIF returns only the number of full or complete years between the two dates.
If the data for the various arguments of this function are not entered correctly the following error values appear in the cell where the DATEDIF function is located:
Average Formula based on year, month, day
Column C1 contains the DOB.
Column D1 contains the Current Date
Column E1 contains the time between C1 and D1 in years, months, days
It's difficult to average column E directly because the results in that column are text values, better to get a result by using DATEDIF on the averages of columns C and D, e.g. to get the average in the same format try
=DATEDIF(AVERAGE(C1:C500),AVERAGE(D1
500),"y") & " years " & DATEDIF(AVERAGE(C1:C500),AVERAGE(D1500),"ym") & " months " & DATEDIF(AVERAGE(C1:C500),AVERAGE(D1500),"md") & " days"
It won't matter if some rows are blank as long as all rows have either both dates or neither
[or if you want to shorten that formula a little and avoid repetion of the AVERAGE functions you could just put the average formula for columns C and D in two cells and then use those cells in the DATEDIF formula....]
Re: Average Formula based on year, month, day
Try this:
Excel 2007
B
C
D
E
1
5.3 years 6.07 months 12.7 days
1/1/2000
10/12/2008
8 years 9 months 11 days
2
1/1/2000
9/13/2002
2 years 8 months 12 days
3
1/1/2000
12/3/2007
7 years 11 months 2 days
4
1/1/2000
5/6/2006
6 years 4 months 5 days
5
1/1/2000
6/18/2005
5 years 5 months 17 days
6
1/1/2000
4/17/2002
2 years 3 months 16 days
7
1/1/2000
9/29/2003
3 years 8 months 28 days
8
1/1/2000
2/15/2003
3 years 1 months 14 days
9
1/1/2000
12/7/2006
6 years 11 months 6 days
10
1/1/2000
2/9/2004
4 years 1 months 8 days
11
1/1/2000
4/12/2005
5 years 3 months 11 days
12
1/1/2000
1/6/2008
8 years 0 months 5 days
13
1/1/2000
10/21/2005
5 years 9 months 20 days
14
1/1/2000
7/17/2007
7 years 6 months 16 days
15
1/1/2000
11/26/2003
3 years 10 months 25 days
16
1/1/2000
6/5/2008
8 years 5 months 4 days
17
1/1/2000
3/20/2003
3 years 2 months 19 days
18
1/1/2000
8/14/2007
7 years 7 months 13 days
19
1/1/2000
12/17/2004
4 years 11 months 16 days
20
1/1/2000
4/12/2007
7 years 3 months 11 days
21
1/1/2000
6/7/2010
10 years 5 months 6 days
22
1/1/2000
3/21/2010
10 years 2 months 20 days
23
1/1/2000
10/23/2001
1 years 9 months 22 days
24
1/1/2000
8/2/2005
5 years 7 months 1 days
25
1/1/2000
9/4/2007
7 years 8 months 3 days
26
1/1/2000
10/7/2007
7 years 9 months 6 days
27
1/1/2000
6/15/2001
1 years 5 months 14 days
28
1/1/2000
5/24/2008
8 years 4 months 23 days
29
1/1/2000
7/22/2003
3 years 6 months 21 days
30
1/1/2000
11/7/2004
4 years 10 months 6 days
Sheet1
Array Formulas
Cell
Formula
B1
{=ROUND(AVERAGE(DATEDIF(C1:C30,D130,"y")),2) & " years " & ROUND(AVERAGE(DATEDIF(C1:C30,D130,"ym")),2) & " months " & ROUND(AVERAGE(DATEDIF(C1:C30,D130,"md")),2) & " days "}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
Excel DATEDIF Function
Note: If you are experienced using Excel functions try the Excel DATEDIF Function Text-only Tutorial which is a bare bones tutorial on the Date Difference function.
This tutorial is intended for users new to using Excel's date functions.
Calculate the Number of Days Between Two Dates in Excel
Excel has several built in date functions that can be used to calculate the number of days between two dates.
Each date function does a different job so that the results differ from one function to the next. Which one you use, therefore, depends on the results you want.
Excel Date Difference Function
The DATEDIF function can be used to calculate the time period or difference between two dates. This time period can be calculated as the number of days, months, or years between the two dates.
Uses for this function include planning or writing proposals to determine the time frame for an upcoming project. It can also be used, along with their birth date, to calculate a person's age in years, months, and days.
DATEDIF Function Undocumented
An interesting point about DATEDIF is that it is an "undocumented" function which means it is not listed with other Date functions under the formula tab in Excel.
To use DATEDIF you must type it manually into a cell on the worksheet rather than using the dialog box method available for other functions.
The DATEDIF Function's Syntax and Arguments
A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.
The syntax for the DATEDIF function is:
= DATEDIF ( start_date , end_date , "interval")
The function has three arguments that need to be entered as part of the function:
Start_date - (required) the start date of the chosen time period. The actual start date can be entered for this argument or the cell reference to the location of this data in the worksheet can be entered instead.
End_date - (required) the end date of the chosen time period. As with the Start_date, enter the actual end date or the cell reference to the location of this data in the worksheet.
"Interval" - (required) tells the function to find the number of days ("D"), complete months ("M"), or complete years ("Y") between the two dates.
Note: When entering the Interval argument you must include the quotation marks such as "D".
More on the Interval Argument
The Interval argument can also contain a combination of days, months, and years in order to increase the variety of answers returned by the function.
- "YM" - calculates the number of months between two dates as if the dates were in the same year
- "YD" - calculates the number of days between two dates as if the dates were in the same year
- "MD" - calculates the number of days between two dates as if the dates were in the same month and year
For help with this example, see the image above.
Since the DATEDIF function can be used to find either the number of days, the number of months, or the number of years between two dates, this example will show separately how to calculate all three between the dates of July 9, 2010 and November 2, 2012.
Note: To avoid calculation problems that can occur if dates are accidentally entered as text, the DATE function will be used to enter the dates used in the function.
Also Note: When manually entering a function in Excel you must type a comma between each argument to act as a separator.
Entering the Data
- Enter the following data into the appropriate cell:
D1 - Start Date:
D2 - End Date:
D3 - Difference (Days):
D4 - Difference (Months):
D5 - Difference (Years):
E1 - =DATE(2010,7,9)
E2 - =DATE(2012,11,2)
Calculating the Difference in Days
- Click on cell E3 to make it the active cell - this is where the number of days between the two dates will be displayed
- Type " = datedif " in cell E3
- Type an opening round bracket " ( " after the function name
- Click on cell D1 to enter this cell reference as the Start_date argument for the function
- Type a comma ( , ) in cell E3 following the cell reference D1 to act as a separator between the first and second arguments
- Click on cell D2 in the spreadsheet to enter this cell reference as the End_date argument
- Type a second comma ( , ) following the cell reference D2 .
- For the Interval argument, type the letter D in quotes ("D" ) to tell the function we want to know the number of days between the two dates
- Type a closing bracket " ) "
- Press the ENTER key on the keyboard
- The number of days - 847 - should appear in cell E3 of the worksheet
- When you click on cell E3 the complete function =DATEDIF (E1, E2, "D") appears in the formula bar above the worksheet
DATEDIF returns only the number of full or complete months between the two dates.
- In cell E4, repeat steps 2 - 7 above
- For the Interval argument, type the letter M in quotes ("M" ) in cell E4 after the second comma to tell the function we want to know the number of months between the two dates
- Type the closing bracket " ) "
- Press the ENTER key on the keyboard
- The number of complete months - 27 - should appear in cell E4 of the worksheet
- When you click on cell E4 the complete function =DATEDIF (E1, E2, "M") appears in the formula bar above the worksheet
DATEDIF returns only the number of full or complete years between the two dates.
- In cell E5, repeat steps 2 - 7 of the Difference in Days Example above
- For the Interval argument, type the letter Y in quotes ("Y" ) in cell E5 after the second comma to tell the function we want to know the number of years between the two dates
- Type the closing bracket " ) "
- Press the ENTER key on the keyboard
- The number of complete years - 2 - should appear in cell E5 of the worksheet
- When you click on cell E5 the complete function =DATEDIF (E1, E2, "M") appears in the formula bar above the worksheet
If the data for the various arguments of this function are not entered correctly the following error values appear in the cell where the DATEDIF function is located:
- #VALUE! : appears in the answer cell If one of DATEDIF 's arguments is not a valid date (if the date was entered as text for example)
- #NUM!: appears in the answer cell if the Start_date is larger (later in the year) than the End_date argument
Average Formula based on year, month, day
Column C1 contains the DOB.
Column D1 contains the Current Date
Column E1 contains the time between C1 and D1 in years, months, days
It's difficult to average column E directly because the results in that column are text values, better to get a result by using DATEDIF on the averages of columns C and D, e.g. to get the average in the same format try
=DATEDIF(AVERAGE(C1:C500),AVERAGE(D1
500),"y") & " years " & DATEDIF(AVERAGE(C1:C500),AVERAGE(D1500),"ym") & " months " & DATEDIF(AVERAGE(C1:C500),AVERAGE(D1500),"md") & " days"It won't matter if some rows are blank as long as all rows have either both dates or neither
[or if you want to shorten that formula a little and avoid repetion of the AVERAGE functions you could just put the average formula for columns C and D in two cells and then use those cells in the DATEDIF formula....]
Re: Average Formula based on year, month, day
Try this:
Excel 2007
B
C
D
E
1
5.3 years 6.07 months 12.7 days
1/1/2000
10/12/2008
8 years 9 months 11 days
2
1/1/2000
9/13/2002
2 years 8 months 12 days
3
1/1/2000
12/3/2007
7 years 11 months 2 days
4
1/1/2000
5/6/2006
6 years 4 months 5 days
5
1/1/2000
6/18/2005
5 years 5 months 17 days
6
1/1/2000
4/17/2002
2 years 3 months 16 days
7
1/1/2000
9/29/2003
3 years 8 months 28 days
8
1/1/2000
2/15/2003
3 years 1 months 14 days
9
1/1/2000
12/7/2006
6 years 11 months 6 days
10
1/1/2000
2/9/2004
4 years 1 months 8 days
11
1/1/2000
4/12/2005
5 years 3 months 11 days
12
1/1/2000
1/6/2008
8 years 0 months 5 days
13
1/1/2000
10/21/2005
5 years 9 months 20 days
14
1/1/2000
7/17/2007
7 years 6 months 16 days
15
1/1/2000
11/26/2003
3 years 10 months 25 days
16
1/1/2000
6/5/2008
8 years 5 months 4 days
17
1/1/2000
3/20/2003
3 years 2 months 19 days
18
1/1/2000
8/14/2007
7 years 7 months 13 days
19
1/1/2000
12/17/2004
4 years 11 months 16 days
20
1/1/2000
4/12/2007
7 years 3 months 11 days
21
1/1/2000
6/7/2010
10 years 5 months 6 days
22
1/1/2000
3/21/2010
10 years 2 months 20 days
23
1/1/2000
10/23/2001
1 years 9 months 22 days
24
1/1/2000
8/2/2005
5 years 7 months 1 days
25
1/1/2000
9/4/2007
7 years 8 months 3 days
26
1/1/2000
10/7/2007
7 years 9 months 6 days
27
1/1/2000
6/15/2001
1 years 5 months 14 days
28
1/1/2000
5/24/2008
8 years 4 months 23 days
29
1/1/2000
7/22/2003
3 years 6 months 21 days
30
1/1/2000
11/7/2004
4 years 10 months 6 days
Sheet1
Array Formulas
Cell
Formula
B1
{=ROUND(AVERAGE(DATEDIF(C1:C30,D1
30,"y")),2) & " years " & ROUND(AVERAGE(DATEDIF(C1:C30,D130,"ym")),2) & " months " & ROUND(AVERAGE(DATEDIF(C1:C30,D130,"md")),2) & " days "}Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself