How to Use Excel Dates as Serial Dates in TM1
TM1 dates and Excel dates have been in conflict since the birth of TM1. TM1 used a serial number of 1 for its start date of 1-Jan-1960, while in Excel for Windows serial date 1 is 1-Jan-1900. This means that Excel stores serial dates starting with the number 1 representing 1-Jan-1900, while TM1 stores dates as a serial number starting with a 1 representing 1-Jan-1960, some 21,915 days later. To allow for this serial date difference, you had to do all sorts of adjustments in rules or TI processes to adjust dates collected using Excel to get them into a form usable by TM1.
This was ok when you were using a cube viewer, but was difficult to manage when you had a TM1 view presented through Excel. There were various ways of treating it, but now with the release of Planning Analytics 2.0 IBM has introduced a parameter that can be used in the tm1s.cfg file for a model that can tell TM1 to use Excel dates. Therefore you can now use the same serial number when presenting a date through Excel and in all other forms of viewing TM1 data.
The UseExcelSerialDate Parameter for Consistent Use of in TM1 and Excel
The new tm1s.cfg parameter UseExcelSerialDate will set TM1 to use the same date value as Microsoft Excel. For example, if you enter 42500 into TM1 with this parameter set, it will display 10-May-2016. If you enter the same value into Excel, it will also display 1-May-2016. Thus you can now present a TM1 view in Excel and have the direct entry of dates via Excel and have them stored and used correctly in TM1.
This first screenshot shows standard Excel, where 42500 is the same as 1-May-2016.
Then we have a cube viewer showing two columns representing either the value or displayed date for that value.
Finally, we have that cube view presented through Perspectives, where we are in the middle of entering 15-5-18 and then the formatted result of that and the subsequent saving of the serial date number into TM1.
To use the UseExcelSerialDate Parameter to Make TM1 Dates Behave Like Excel Dates
To use the parameter, locate the tm1s.cfg file for your model and edit it to include this new parameter:
Then restart your TM1 service and away you go.
Be careful though – you probably don’t want to do this for a model that is already in existence – especially if it has lots of date calculations using 1 as 1-Jan-1960!