Working with the DATE Function

Working with dates in Microsoft Excel can be a puzzling and, at times, aggravating experience. Adding days, months, or years to a date can be at first a mystifying task.

Happily there are some excellent 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(2010, 6, 17), it will return today’s date of June 17, 2010.

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.