When working with dates, we are normally to calculate the time between the two dates in years or in months. However, you can also calculate the exact time between the two dates in years, months and days using the DATEDIF function.
For example, from January 1, 2015 to February 2, 2016, it is 1 year 1 month and 1 day. You can use one date to minus another date to get the number of days between. However, to calculate the exact time between the two dates, please use the following formula:
=DATEDIF(A2,B2,"Y")&" Years "& DATEDIF(A2,B2,"YM")&" Months "&DATEDIF(A2,B2,"MD")&" Days"
Where A2 is the start date and B2 is the end date.
– DATEDIF(A2,B2,"Y") returns the number of complete years between the two dates A2 and B2;
– DATEDIF(A2,B2,"YM") returns the number of months when ignoring the years and days;
– DATEDIF(A2,B2,"MD") returns the number of days when ignoring the years and months.
For example, there are "8 Years 2 Months 5 Days" from 1995-08-04 to 2003-10-09.
The only program is that the word "Years", "Months" and "Days" will apply all situations even there is only 1 year, 1 month or 1 day.
To fix the program, you can apply the IF function to return Year, Month, Day when the value is 1 and Years, Months, Days when the value is not equal to 1.
=DATEDIF(A2,B2,"Y") & IF(DATEDIF(A2,B2,"Y")=1, " Year "," Years ") & DATEDIF(A2,B2,"YM") & IF(DATEDIF(A2,B2,"YM")=1, " Month ", " Months ")&DATEDIF(A2,B2,"MD") & IF(DATEDIF(A2,B2,"MD")=1," Day"," Days")
Where A2 is the start date and B2 is the end date.
If you want to know your age in years, months and days, you can use the same formula and replace B2 with today() function.
=DATEDIF(A2,today(),"Y") & IF(DATEDIF(A2,today(),"Y")=1, " Year "," Years ")& DATEDIF(A2,today(),"YM") & IF(DATEDIF(A2,today(),"YM")=1, " Month ", " Months ") & DATEDIF(A2,today(),"MD") & IF(DATEDIF(A2, today(),"MD")=1," Day"," Days")
Where A2 is your date of birth, and today() function for today's date.
For example, today is May 22, 2016 and your date of birth is August 10, 1993, you are 22 Years 9 Months 12 Days old.