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:

The wonderfully elusive DATEDIF function. Documented only once...in 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.

Post a Comment