Wednesday, September 19, 2012

An undocumented Excel Function!

I know it could be boring for you to learn about Excel functions, which are available in plenty, and on which Excel Help offers good insight. So, I chose to write about an Excel function today that exists and can be used but on which Excel Help has nothing to say.

You would know that the default way to find difference between two dates is to just use the subtract formula and format the result cell as number. For instance, if Cell A1 contains startdate and A2 contains the enddate, you would enter the formula “=A2-A1” in the result cell, and format it as number to see the days.

Today we are going to learn about a date related function, which is an undocumented Excel function – and is actually a nice utility for computing the difference between two dates. Beauty of this function is that you can get the difference expressed in days, months, years or a combination of any of these. The function is called “DATEDIF” and can produce some unimaginable (positivelyJ ) results, as we would see in the examples that follow.

Formula Syntax:
=DATEDIF(startdate, enddate, interval)

Here, the startdate  and enddate would just be cell references (like A2, A3) which hold the respective dates for which you want to compute the difference. The last parameter, interval, can take one of the following forms (include the quotes):
  • “d” to get the result in number of days
  • “m” for number of months
  • “y” for number of years
  • “ym” for number of months (ignoring the year)
  • “yd” number of days (ignoring the year)
  • “md” number of days (ignoring the month and year)

How it works:
I believe in the saying “A picture speaks a thousand words”. Take a look at the below screenshot which I have constructed for you - it would make things very clear than any number of explanations.

 Deploying a combination formula:

You can obviously make use of this function and deploy a combination trick to get the exact number of years, months and days neatly in your result. See the following picture to understand this – am giving you two variations, the second one would look more neat because I use an additional IF function to take care of the Zero value situation.

That completes the trick for the day. Spread the word around to your friends, if you liked this and find it to be of some use.

My upcoming book on Excel contains many more such tricks and practical insights into getting Excel work for you. Please take a moment to visit my earlier post to understand details about my new book, and make use of the special offer available now to order it at a discount.

1 comment:

Bill said...

The wonderfully elusive DATEDIF function. Documented only Excel 2002 Help and then never again.
Someone discovered that the DATEDIF from January 31 to March 1 is reported as 1 month and -2 days. Rather than fix this, Microsoft chose to quit documenting it. As a tip, for those of you with PowerPivot, the YEARFRAC function in PowerPivot does the same thing but allegedly handles the Jan 31 to March 1 bug.