UseExcelSerialDate

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.

UseExcelSerialDate=T

Default value: F

Example

You can see the serial date issue in this example.

The following rule returns May 26, 2015 as a serial date.

[]= N:(DAYNO('2015-05-26'));

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.