Date Calculations in Excel
With Microsoft Excel Date Calculations, is there an easy way to work out current age using an Excel formula?
Yes! a function called DATEDIF used to work really well. Unfortunately Microsoft no longer supports this function in Excel – you can still use it, but you just don’t get any help with it. For example, you have to remember the order of the arguments that go into the function, there’s no handy pop-up box to give you a hint. So you may end up typing it in multiple times with the arguments in different orders until it works… here’s a handy guide to the syntax:
=DATEDIF (Earlier Date, Later Date, Time Interval)
The Earlier Date will be the date you are working with, for example the Date of Birth. The Later Date will be today’s date, which you can get with the TODAY function. The Time Interval is how you want to express the difference between the two dates. If you are working out age, then you will need to specify the interval as years by using the code “y” for year. If your Date of Birth is in cell A1, then the completed formula will look like this:
=DATEDIF(A1,TODAY(),”y”)
The DATEDIF function automatically takes into account if the person’s birthday has already occurred this year. If not, it will subtract one from the resulting age.
Alternative Microsoft Excel Date Calculations:
If you don’t want to use the unsupported DATEDIF function, then you can still work out an age from a Date of Birth with some nested Date Functions. There is a useful function called YEAR that will take a date and return the year bit of that date. For example, if you have the date 01/Dec/1982 in cell A1 then =YEAR(A1) will return 1982.
You can combine two YEAR functions to work out the difference in years between two dates:
=YEAR(TODAY())-YEAR(A1)
At the time of writing the date is May 2016, so the above formula would give 2016-1982, which equals 34. Sounds good so far, yes? Except, of course, if my birthday is in December I’d rather still be 33!
So, to make this work, we need to somehow decide whether or not the anniversary of the date has passed. If it hasn’t, then we need to subtract one from the resulting age. This is the fancy bit!
In fact there are several ways to do this; what follows is, I think, a relatively straightforward method. We are going to work out the day of the year for each date. By this I mean: which day, out of 365, have we reached within the year in question? We can then compare the two numbers, and if the day-of-the-year number for our date of birth is less than the day-of-the-year number for today’s date, then we haven’t had our birthday yet.
Let’s break this down into parts. The first part will be to work out the day-of-the-year-number for our date of birth. We can do this by taking the date of birth and subtracting the 1st of January of that year from it. When you subtract one date from another, you get the difference between those dates in days. To create the 1st of January for year X we use the YEAR function, which we have already seen, and the DATE function. The DATE function creates a date from the three parts we supply: the year, month and day. If the original date is 01/Dec/1982, which is in cell A1, then the formula is:
=A1-DATE(YEAR(A1)),1,1)
This gives us the difference is days between the date in A1 and the 1st of January 1982. (It takes leap years into account)
If we do the same for today’s date, using the TODAY function we can produce something like this:
The next step is to compare these two values using an IF function and return either 1, if the birthday hasn’t happened, or 0, if it has.
=IF(A1-DATE(YEAR(A1),1,1)>TODAY()-DATE(YEAR(TODAY()),1,1),1,0)
If the day-of-the-year-number for the date of birth is bigger than today’s day-of-the-year number, then this means you haven’t had your birthday yet.
The final step is to put all this together into one long formula. It looks pretty scary, but if you break it down into its component parts it’s not so bad.
=YEAR(TODAY())-YEAR(A1)-IF(A1-DATE(YEAR(A1),1,1)>TODAY()-DATE(YEAR(TODAY()),1,1),1,0)
Finally, format the result of this formula as a number.