DATEDIF: Calculating date differences by year, month, day, and more

A common need when working with date data, particularly when it comes to hire dates, birth dates, anniversary dates, is to come up with a quick way to calculate the amount of time elapsed between, for example, a hire date and today's date or a birth date and today's date. In Column F, are various hire dates in this list of some 700 names and in Column G there is a function that is not very well-documented in Excel. It's called DATEDIF. And you will notice here that you don't see any prompting as to what the arguments of it are, but this function does wonders in a lot of different situations.

For example, here we want to know, how long this person has been here. I am recording this in mid-July of 2010. DATEDIF begins with a starting date, in this case it's July 5th of 2003. I am using today's date here as a dynamic entry, and we want to see the answer in years. How many years has this person been here? And it's been seven years. Now if this person had been hired in August, you might say, "well, that's close enough, isn't it? I mean, it's almost seven years," but if I change this to eight, you will see that no, that's not the case based on the time I am recording this. And this is accurate to the day and you can test it out as you wish.

So DATEDIF allows us to come up with the number of years. Now it's highly unlikely that in this kind of data that you would want to know how many months has been here. We'd certainly change the heading if we did this, but if we put in m here and by the way the y didn't have to be capitalized,. Either way it works. M means the number of months, 83 months, and we could go to a third step here, and you probably wouldn't want to know how many days and this isn't counting working days either, but it's simply the number of days between those two dates.

So a variety of ways to use this. I think in this case it's pretty obvious what makes sense. And so the beauty of this too is that this is always accurate and up-to-date, because we are working off of a running total. And you could say, since we have about 700 names in this particular list, every time we open this file, if we open it once a day, a couple of the year entries here probably have changed on the average,. Because today is a changing day. Now we can use this in other ways as well. We might want to know how much time has elapsed here, and again if this relates to employees and anniversary dates, sometimes it's important to know how many months it's been since the last year of the anniversary or how many days it's been since the last year or the last month's change.

So DATEDIF has some other possibilities as well. Typical function in Excel at this point right now, you would get some prompting regarding the arguments. This doesn't happen with DATEDIF. So in this case we are talking about a starting date here of September 20th in 2010, comma, this date right here, comma, and we might want to know how many months it is or days or years, as we have already suggested, but we might also want to know how many months beyond a given year this might have been. So "ym" entries here can only be the entries 0 through 11.

Okay, what do we get here in this case? It's seven months and we can imagine it's going to be five months until the next anniversary here, so it's seven months. How many months beyond the year? Hhow many days has it been since the last year these totals represent? 240 days. In another 125 days or so it would have been a year. These are perhaps not nearly as likely to be used as the ones we saw on the previous worksheet, but you get the sense here of some real capability here and you could also put this together if you are familiar with text functions in a string to actually build a sentence to indicate how many years and months and date this time lapse has been.

So it's a powerful feature, and again it's strangely not documented in Excel. If you go to the Formulas tab and click on the Date & Time functions, you do not see this entry in here. I am not sure why Microsoft does this, but I think it's a great feature. I use it all the time. It's the ideal function for calculating birthdays and anniversaries, as well as some other unusual uses as well.