This is a guest post from Revelwood’s Shane Bethea.

Did you know IBM Planning Analytics 2.0 has a new feature that makes it easier to manipulate dates?

Previously, when manipulating dates in IBM Planning Analytics / TM1, I always had to reach for a crumpled up, barely legible sticky note I have laying on my desk that has the number 21916 written on it.  This is because TM1 rules and TI functions do not calculate serial date numbers from the same starting point as Microsoft Excel.  The starting point for time in TM1 is 1/1/1960 whereas the starting point for time in Excel is 1/1/1900.  The difference between 1/1/1900 and 1/1/1960 is, you guessed it, 21916 days.  Date functions in TM1 rules and TI have to take this into account by adding or subtracting 21916 when manipulating dates.

Wouldn’t it be nice to eliminate that obscure number every time you work with dates in TM1 / Planning Analytics?  Well, IBM has introduced a configuration setting in Planning Analytics 2.0 called ‘UseExcelSerialDate’ to remove the confusion. When this new configuration setting is set to T (true), TM1 / Planning Analytics rules and TI functions use a starting point of 1/1/1900.  This configuration setting can be turned on by adding the parameter to the tm1s.cfg file for your TM1 database.  The default setting is F (false).

Let’s take a look at an example. In TM1 version 10.2.2 and earlier, the date 5/31/2018 is represented by the number 21335 because 5/31/2018 is 21334 days after 1/1/1960.  However, in Microsoft Excel, the date 5/31/2018 is represented by the number 43251.  This is because 5/31/2018 is 43250 days after 1/1/1900.

Below is a simple cube rule to calculate the serial data number based on user entry of a date.  Because the UseExcelSerialDate configuration setting is false by default, I have to add 21916 to ensure that TM1 / Planning Analytics and Excel dates match.  See below where Index 001 uses only the DAYNO() function and Index 002 uses the DAYNO() function plus 21916.

Now, if I set the UseExcelSerialDate configuration setting to T, I can modify the rule to not include the addition of the 21916 and my Serial Date measure results in the value of 43251, which matches Excel serial dates.

This new configuration setting enables my TM1 functions to calculate on the same scale as Excel and removes any confusion about dates that may have existed in the past.  Best of all, I can get rid of that old sticky note.

IBM Planning Analytics  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!

Read more blog posts with tips & tricks on IBM Planning Analytics and TM1:

IBM Planning Analytics Tips & Tricks: The Ranked Report

IBM Planning Analytics Tips & Tricks: Using MDX to Compare Dimension Hierarchies

IBM Planning Analytics Tips & Tricks: New Parameters for Turbo Integrator