I was looking for a way to compare the total years between two dates in decimal format. My brain immediately goes to the process of how I would do that calculation if I am were writing it in a programming language, but Excel VBA and Excel formulas are a different beast.
In Excel, do you use DATEDIF, “sum’ing” with TODAY() or NOW(), or something else?
Excel has lots of little functions that do simple date operations (and lots of other stuff too). In my searches, I came across something called YEARFRAC.
It’s very simple to use, and takes three parameters: a start date, end date, and an optional setting for controlling the number of calendar days you’re working with for the locale and calendar you’re using (I used Option 1).
Quite simply, you supply those three parameters and Excel will return a number in the form of a decimal that is the sum of the years between the two dates. Slick!
Here’s what it looks like in Excel:
This would be handy for calculating service anniversaries, ages, or anything else where you need to know the difference between two dates.
You can find the syntax and usage reference for YEARFRAC here.