Tuesday, November 8, 2016

Dates, Dates, Dates

No, we are not talking dates and figs today…

As most experienced Excel masters will tell you, working with dates in Microsoft Excel can occasionally be a maddening experience for many users.  Upon closer inspection, however, there are some excellent Date/Time functions built into Excel, and one of the most versatile is the (cleverly named) “DATE” function.

The syntax of the DATE function is =DATE(Year, Month, Day), therefore if you enter as follows, =DATE(2016, 11, 8), it will return today’s date of November 8, 2016.

What is distinct about the DATE function is its Flexibility.  It can, for instance, accept inputs from cells, functions, and calculations. For example, =DATE(2010+1, 6, 17) returns June 17, 2011. =DATE(2016, 11, 8+47) returns December 25, 2016 (Merry Christmas!). Pretty Cool!

So let’s say that you have an interactive What If report that has cell B1 with a continuously updated current date, (The =TODAY() function is an obvious choice), and cell C1 displaying the date which is a variable of numbers of years in the future based on the value you place in cell A1. Your formula in C1 would look like the following:

=DATE(YEAR(B1) + A1, MONTH(B1), DAY(B1))

If today is November 8, 2016 and you have the number 4 in cell A1, the above formula would return November 8, 2020.

The DATE function is worth taking some time to get to know. Make it a “Date” to remember…

No comments: