IBM Planning Analytics Tips & Tricks: The Excel DATE Function

June 9, 2020

Many of you already know about Excel’s DATE function. The function is used to create a date by defining specific values for the year, month and day.

DATE(2020,7,20) gives a result of 7/20/2020.

And some of you know that you can also use formulas to calculate a date.

If a value of 5 exists in cell C4 then

DATE(2020,7,20+C4) gives a result of 7/25/2020.

But did you know that the formula will also check for valid dates and, if needed, shift the month automatically?

DATE(2020,7,35) gives a result of 8/4/2020

(since there are not 35 days in July!)

and

DATE(2020,7,30+C4) gives the same result

Not only can this formula be used to calculate variable dates, but it will also ensure that you don’t incorrectly calculate a value that doesn’t exist on the calendar. After all, we wouldn’t want Julius Caesar coming back to question our use of “his” month!

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Author

Lee Lazarow

View all posts