Enables the use of Microsoft Excel serial dates instead of Planning Analytics serial dates.
Parameter type: optional, static
When UseExcelSerialDate is enabled, Planning Analytics rules functions and TurboIntegrator functions use Jan 1, 1900 as a base date for serial dates instead of Jan 1, 1960.
In the past, Planning Analytics rules functions used serial dates that represent the number of days elapsed since Jan 1, 1960. This conflicts with Microsoft Excel serial dates, which represent the number of days elapsed since Jan 1, 1900. The number formatting features in Planning Analytics expect cube data to use Microsoft Excel serial dates rather than Planning Analytics serial dates.
To avoid the need to convert dates, enable UseExcelSerialDate to have rule functions use Microsoft Excel dates rather than legacy Planning Analytics dates.
Default value: F
You can see the serial date issue in this example.
The unformatted result is 20234, which indicates that 20234 days have elapsed since Jan 1, 1960. The rule function is using legacy Planning Analytics serial dates.
When you set the display format in TM1® to a date format, such as mmmm dd, yyyy, the result is May 25, 1955, because May 25, 1955 is 20234 days away from Jan 1, 1900. The value is being interpreted as a Microsoft Excel serial date.
After you add
UseExcelSerialDate=T to the Planning Analytics database configuration and restart the
database, you see the expected result, May 26, 2015, in Planning Analytics. The rule function is now using Jan 1, 1900 as
the base date for serial dates.