Thursday, June 17, 2010
Working with the DATE Function
What is particularly Cool about the DATE function is that it very flexibly accepts inputs from cells, functions, and calculations. For example, =DATE(2010+1, 6, 17) returns June 17, 2011. =DATE(2010, 6, 17+7) returns June 24, 2010. You get the picture.
The DATE function can handle more complex situations as well. For instance, =DATE(2010, 6+9, 17) returns March 17, 2011 (Hey, isn’t that St. Patrick’s Day?...).
So let’s say that you have an interactive What If report that has cell B1 with a continuously updated current date, (you can use the =TODAY() function for that), 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 June 17, 2010 and you have the number 4 in cell A1, the above formula would return June 17, 2014.
Take a few minutes and experiment with the DATE function. It is one “date” that will never disappoint.