Warning: Undefined array key "oxygen-cache-1409" in /var/www/wordpress_jb/wp-content/plugins/jetpack/class.jetpack.php on line 5759

Warning: Undefined array key "oxygen-cache-1414" in /var/www/wordpress_jb/wp-content/plugins/jetpack/class.jetpack.php on line 5759

Warning: Undefined array key "oxygen-universal-styles" in /var/www/wordpress_jb/wp-content/plugins/jetpack/class.jetpack.php on line 5759

Useful Excel Functions: YEARFRAC

Posted on February 13, 2015

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:

2015-02-13_14-54-56

 

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.

About JB

I've been a technologist for over 20 years, getting my start in IT with a large Fortune 100 international retailer in the 1990s. I love to talk about new technology, mobile devices, software development, photography, and the weather.  The opinions expressed on this blog are my own.

Follow me

Copyright © Justin Braun.  All rights reserved.
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram