Join Dennis Taylor for an in-depth discussion in this video DATEDIF: Calculating date differences by year, month, day, and more, part of Excel 2007: Working with Dates and Times.
A powerful date function, but one that is strangely not present under Date & Time as we click on the Formulas tab categories here for Date & Time functions, is a function called DATEDIF. It's not here, and yet, I am using it here in column G, and here how it's being used in cell G3. We are trying to calculate how long a person has been in this organization. I am using a function called TODAY, and at the time of this recording, it's mid-July of 2010, and you can see clearly here, this person has been here seven years, and notice that there is a Y out here.
The DATEDIF function requires that we begin with a starting date, and then in this case an ending date, which is actually the dynamic entry, TODAY's date. This is the function TODAY that's using today's date. You notice below that there is no prompting for arguments here, unlike typical Excel functions. So this is a strange thing indeed. Now, as I press Enter here, and you saw the result earlier, it is a 7, and as you look at some of the other examples here, again, imagine we are talking about mid-July of 2010. The anniversaries are working, and it does work to the day, exactly the way we use anniversaries and birth dates.
In addition to Y, we could also use M to calculate months or D to calculate days. There are some other variations on this, as well. So in a different worksheet here called DATEDIF Examples, let's calculate, for example, the number of months a particular project or a person that we are referring to in columns A and B. We have got a time lapse between the two entries here. So DATEDIF, left parenthesis, starting date in this case is in cell A2, comma, the ending date is in B2.
This time we want to calculate the number of months, "m", and a quick double-click here will copy this down the column. So we see in the other examples here, and here is one we can calculate most easily with our eyes, just looking at dates within the year, now 2011, that's a six month interval. Notice that it's almost seven months, but well, not quite, and that's 27 days, so it's six months and 27 days. Even if it were six months and one day short, it's still going to indicate six months. We are calculating the difference here using m.
Now, we could do this with days, but that's just more easily achieved simply by subtracting the two. But there are two other ways we could use this as well. We might want to know how many days after a certain monthly anniversary, perhaps. Now, that might sound a little contrived, but what if we were trying to tabulate a date difference here between these two days, as before, the one in A2 and the one in B2. This time we want to know how many days it's been since the last monthly anniversary.
So within double quotes now, we will put in "md", the number of days after the monthly anniversary: eight days from the twentieth to the twenty-eighth. And we can see what's happening in other examples as well here, the number of days after the monthly anniversary. We can also do this, meaning the number of days after the last yearly anniversary. Put in a Y here. So this could be as high as 364, and we see the entries in here. So I can see the example here.
We're three shot of a year here, as we use 'yd'. So, various uses for this. Again, I think it's most effectively used in the first example that we saw. In a dynamic database, you always want to know how many years someone has been here, but you don't want to have to constantly recalculate this. So using the TODAY function as the later date, this allows us to tabulate a higher date. And certainly in a different kind of database, where you have a birthday in, you would have a column called age, and this would calculate age to the day.
And once again, it's a little bit strange in that you do not find documentation on this function in Excel. You will see it in various Excel books, but not within Excel itself. But a great tool for calculating differences in a variety of different ways between dates.
- Understanding how Excel records and stores dates and times
- Looking at standard date/time entry options and acceptable alternatives
- Using the TODAY and NOW functions
- Customizing date formats
- Exploring keyboard shortcuts
- Formatting time for hours over 24
- Calculating differences across dates and times
- Rounding calculations
- Working with holidays
- Validating with dates
- Converting formatted dates to usable data