How to calculate date difference in Microsoft Excel 2010?
Microsoft Excel 2010 is a vast application. There are a number of hidden functions in it that you can explore for presenting your data in a better way.
Very often, we need to calculate the time lapse between two dates. There is a specific function for this in Excel 2010 called ‘DATEDIF’. This function calculates the difference between two dates. This is a hidden function; you can see it in the Formula tab of the Excel application.
It is important to learn the syntax of ‘DATEDIF’ function first as you would not get the desired results without this. It’s syntax is as follows:
=DATEDIF(Start_Date, End_Date, Unit)
- Start date: Starting date of the period
- End date: Ending date of the period
- Unit: Interval by which you want the difference to be returned
There are 6 variations of unit which are as follows:
Y: Period difference in number of years
M: Period difference in number of months
D: Period difference in number of days
MD: Difference between the days in ‘Start_Date’ and ‘End_Date, ignoring months and years
YM: Difference between the months in ‘Start_Date’ and ‘End_Date’, ignoring days and years
YD: Difference between the days in ‘Start_Date’ and ‘End_Date’, ignoring years of the dates
- As an example, let’s find out the difference between numbers of days from 15 April 1987 till today’s date by applying the ‘DATEDIF’ function.
- Type the syntax as follows and press ENTER.
Note here, we took 15 April 1987, as the START_DATE, and used the function ‘Today ()’ for current date as END_DATE If you give today’s date manually, the result will not update automatically. Today () function takes the current date from the system automatically. We need to find the number of days between the two periods, so we used “d” as unit.
We will take another example to find the age of a person. Suppose John was born on 6 January 1985. We need to calculate his age in years, months and days.
- Type the formula as follows:
- =DATEDIF(C2,TODAY(),"y")&" years,"& DATEDIF(C2,TODAY(),"ym") &" month(s), "& DATEDIF(C2,TODAY(),"md")&" Days"
We get the result as 30 years, 3 months, 21 days. Note here that we have combined the three instances of ‘DATEDIF’ function together with different units (y, ym, and md) to get the result. We have also used ‘&’ function to combine the results and entered the relevant texts (years, months, and days) after ‘&’ function to give the output as shown.
As another example, let’s take out the difference between two specific dates without using the ‘TODAY ()’ function and entering the dates manually.
- Suppose we need to find the difference between 01/02/1911 and 04/03/2015
- Type the formula as follows:
We will get the result as 104 years, 3 month(s), 1day(s).
Wasn’t the solution simple to follow? Calculate the date difference on your Microsoft Excel 2010 program using this formula and share with us how you fared! However, if you are not able to do it or are having doubts using Excel 2010, call iYogi technicians any time for instant help. We are on call 24x7.