Have you ever needed to determine the number of days, months or years between two dates? Calculating the number of days between two dats using Excel is pretty simple. Just use a formula to subtract the later date from the earlier date.
For example, if cell B1 contains 1-Jan-2013 and cell B2 contains 03-Mar-2013, you simply enter the formula =B2-B1 in cell B3 to get the number of days. At first the result may look strange. That's because Excel sometimes assumes you are entering another date and automatically formats the result as a date.
However, calculating the number of months or years between two dates isn't so obvious.
There's a Function in Excel that makes this task easy but for some reason Microsoft has hidden it away. You won't even find it in the Paste Functions list (Formulas, Insert Function). The function is called DATEDIF (i.e. date difference).
The syntax for the function is
=DATEDIF(startdate,enddate,"interval").
Let's use the same dates as above in our formula to calculate the number of complete months between the two dates.
=DATEDIF(B1,B2,"m").
A couple of things to keep in mind about the DATEDIF function:
1) The start date must be less
than or equal to the end date, otherwise it will give an error.
2) Acceptable interval codes are "d", "m", "y", "ym", "yd", "md" (with quotes);
3) It may appear obvious what the "ym", "yd", and "md" interval codes mean but they require a second look. The "ym" interval code returns the number of months between the two dates as if they were in the same year and ignores the year. The "yd", and "md" interval codes returns the number of days between the two dates as if they were in the same year and ignores the year.
2) Acceptable interval codes are "d", "m", "y", "ym", "yd", "md" (with quotes);
3) It may appear obvious what the "ym", "yd", and "md" interval codes mean but they require a second look. The "ym" interval code returns the number of months between the two dates as if they were in the same year and ignores the year. The "yd", and "md" interval codes returns the number of days between the two dates as if they were in the same year and ignores the year.
To calculate the number of years, months and days between two dates (more than a year apart) you can use this formula (assuming your start date is in cell B1 and your end date is in cell B2).
=DATEDIF(B1,B2,"y") & " years, " & DATEDIF(B1,B2,"ym") & " months," & DATEDIF(B1,B2,"md") & " days"
To use this formula, Copy and Paste it into your Excel formula bar, then adjust the cell references.
WOULD YOU LIKE TO DOUBLE YOUR
PRODUCTIVITY IN EXEL?
HOW ABOUT TRIPLE OR QUADRUPLE IT?
HOW ABOUT TRIPLE OR QUADRUPLE IT?
"Give me 10 or 15 minutes each week and I will show you how learning a few of my time-saving Microsoft Excel tips on a regular basis will seem almost effortless BUT will quickly help you DOUBLE, TRIPLE, or even QUADRUPLE your productivity in Excel" - Francis Hayes, The Excel Addict
Get my FREE Weekly Newsletter
"Spreadsheet Tips From An Excel Addict"
Now being read by more than 35,000+ Excel Addicts around the world
"Spreadsheet Tips From An Excel Addict"
Now being read by more than 35,000+ Excel Addicts around the world
Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.