Calculate dates efficiently using Excel's hidden function - DATEDIF

- [Voiceover] A powerful yet not so well-known function in Excel is called datediff. And there's something very strange about it. If you had heard about the function, and maybe heard a brief description of the fact that it can handle differences in dates to tell you how many years, months, days have passed. If you forgot about that and came back later and tried to find it by going to the Formulas tab and clicking Date & Time, you will not see datediff here, and yet it does work. And further more, if you're using Excel for the Mac, in recent versions until Excel 2016 for the Mac, you will find it documented.

In the current version, 2016 for the Mac, it's not found there either, although it works, as we're about to see as it does here too in Excel 2016 for Windows. So equal datediff allows us to calculate the difference in dates. And if you look over in column G, I'll scroll over there a little bit, you'll see different kinds of entries that we ultimately can put in here, different kinds of measures. Nearly always when you have an age column, you want to be seeing a formula in that column.

Ages change of course. People get older and we want something that's always up to date. Many many times, the age column should include a function called today, meaning today's date. If for example the heading were age at enrollment, that would be a static entry that would not change, and there are a few other exceptions too. But many many times with age, you want to be sure and check it out, make sure it's a formula. Datediff allows us to calculate the difference between two different dates. In this case, the first date is the birthdate, right there, comma.

The second one is today's date. I'll use the function. Now as I'm typing this, it's mid-May of 2016. The answer that I get might or might not be the same that you will get if you try this a bit later. It depends upon what part of the year you're in and which year, and so on. So in May of 2016, this person right here you can see, not quite 35 years old. You know what the answer should be. It should be 34 if we calculate years. So following the two types of date entries, comma, then we put in one of the six possibilities that we see over in column G.

It must be within double quotes. The letters do not need to be capitalized. I wanna know the age of this person in years. That's good enough right there. I'll complete the entry with Control + Enter since I do want to copy it down the column. And there's the answer. I'll double-click. We'll get the other answers down the column. Again, there's a look at it. You can also see it in the formula bar. And over time, imagine as you open and close this file, these numbers will change. As I'm using this file right now, in a week or so, this number will change.

It'll become 43. So at the time you're looking at this, this might say 43. So it will vary of course since it is a dynamic entry. But the datediff function is designed to come up with an accurate answer to the day. Same idea over here might work with service years. Now all I need to do with the right mouse button is drag this over here. I'll let go of the right mouse button, simply copy. That's 13 years for the service years. Same general idea. We could use that as well here too. Double-click, copy down the column.

I think of all the times I've seen this being used, most of the time, Y seems to be the most common use. But we can certainly use months, and off to the right here, we've got some other data here. We've got some items here that were installed on certain dates. They were deactivated on certain dates. How many months were they in service? And here too we're doing the same kind of rounding, but this time with months. So in the example here, a little bit different because we will not use the today function. Equal datediff. Any time you type a function, as soon as you type in the first letter, you see other functions that begin with that letter, but we don't see any datediff in these possibilities here.

Notice that when we do type in datediff and put in a left parenthesis, we get a little indicator below as if to say, well, that's gonna be okay or valid, but there's no indication as to what we would enter here, as we typically see with other functions. So again, the oddity about this function not being documented, not being available through normal means, yet you can just type it in. So how long had this equipment been in service? Here's our starting date, comma. Here's our ending date over in cell L2, comma, in months, double quote M double quote.

I'll complete the entry with Control + Enter and double-click to copy this down the column. And once again, if you're trying something like this at a later time looking at these same kinds of dates, you're likely to get different answers because time will have passed after this recording. So once again, we're seeing how this is being used. Very straightforward, easy to use. In both this column and in the previous one of course, the column need only be as wide as the actual entries. So eventually we just double-click that boundary there to get our results. Now, there's one oddity and one set of problems when we use this feature, and perhaps this is where we run into the issue of dates that don't really make sense.

Here's a starting date, here's an ending date, and here I'm trying to use that variation called MD. And the description above it pretty much describes what we're trying to do. How many days has it been since the last monthly anniversary? In doing some research on this, the one problem I've found with datediff is dates that involve late January and early March, or late January, late February, that sort of thing. How many days has it been since the last monthly anniversary? So imagine on February 28th, you're comparing this with something on January 29th.

There actually has been no month that's passed just yet, so it's been 30 days since the last monthly anniversary, you might say the zero anniversary. But look what's happening here. January 30th, it's March 1st. Surely more than a month has passed. When was the monthly anniversary for January 30th? Well there is no February 30th, but wouldn't you assume that the software would somehow say that February 28th, this is a non-leap year, would be the monthly anniversary? And this is one day later? We end up with minus one, and we get one here too as we're comparing these two dates.

Now some of the entries seem to be correct. Some don't. I've got a variety of different dates being displayed here, and there are even more examples off to the right. The examples in columns P and Q are for the year 2015. For the year 2016, when there is a leap year, we're getting these kinds of answers, and they're not always the same. And of course we're comparing different sets of dates at different times here too. So if you find yourself liking datediff as I do, you might want to steer clear of this. But for all the different times I've used this function, I rarely have needed to compare dates across this timeframe.

I've experimented a little bit using different starting and ending dates that might involve different months like July and September or something like that, and I never seem to have a problem. It's only this issue here. I was alerted to this four or five years ago as the possible reason as to why Microsoft doesn't document this function. But I think it's a really valuable tool. I think you've seen in the previous examples here how valuable it really can be. Any time you're calculating age or service years, this is likely to be the function to use. It's easy to use, it's simple, and it certainly allows the dynamic use of the today function when necessary as we've seen in these examples.