Calculate your age, using Excel’s DateDif function.

          During this time of the year, most of us find ourselves performing some sorts of date manipulations in Excel: running year-end reports, creating a project plan for the next year, or simply setting up a new calendar. Let’s use one of Excel’s “hidden” functions to calculate one’s age in years, months, and days.

          We remember that Excel stores dates as serial numbers, in consecutive order. According to Microsoft, Day 1 fell on January 1st, 1900. Therefore, today’s date is equivalent to number 42,020 (meaning, 42,020 days passed since January 1st, 1900.) This makes it fairly easily to calculate difference between two dates [in days] by subtracting one date from another. While Excel does not support dates prior to January 1st, 1900 out of the box, if you need to work with such dates, John Walkenbach offers an Excel add in: XDate to help with this task.

          In order to perform date calculations in years or months, we need to use Excel’s DATEDIF function. Don’t try to find documentation on this function within program, for some reason Microsoft only makes it available online . In a nutshell, this function takes in arguments of start_date , end_date , and depending on unit , performs corresponding calculations. Below you will find valid values for the unit parameter:

DATEDIF(start_date,end_date,unit)

          Researching the subject, I found that the first version of Microsoft Excel for Windows was released on October 6th, 1987. Let’s set this date to the start_date, and then store it in cell B1. We will then assign current date TODAY() the value of end_date, and put it in cell B2. We can now perform following calculations:

          Calculate Excel’s age in days:

DATEDIF(B1,B2,"d")

          Calculate Excel’s age in months:

DATEDIF(B1,B2,"m")

          Calculate Excel’s age in years:

DATEDIF(B1,B2,"y")

          To calculate Excel’s age in years, months, and days, we need to use “ym” parameter for months calculations, ignoring days and years; as well as “md” parameter for days calculations, ignoring months and years. I will not use the Nested If syntax to avoid unnecessary complications; however we still need to use the IF function and concatenation operation to format our results as desired:

=IF(DATEDIF(B1,B2,"y")=0,"",DATEDIF(B1,B2,"y")&" year(s), ")&IF(DATEDIF(B1,B2,"ym")=0,"", DATEDIF(B1,B2,"ym")&" month(s), and ")&DATEDIF(B1,B2,"md")&" day(s) old!"

          Please let me know if you need any help adopting this formula for your own needs. While at it, let’s determine day of the week for both dates, using Excel’s TEXT function. Format Text parameter with value “ddd” would show 3 letter abbreviation of week day, while “dddd” displays full name of the day:

=TEXT(B2,”dddd”) will display Friday as today’s day of the week. Perhaps the screenshot with the functions and formulas used will come in handy at this time. Stay tuned for the next post on working with date functions, and leave comments with any further questions.