Exploring TM1 - a Chartertech Company
Search
Close this search box.

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.

Excel Dates in 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:

UseExcelSerialDate=T

Then restart your TM1 service and away you go.

Warning!

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!

  • This field is for validation purposes and should be left unchanged.

Post Sections

Related Posts

John Vaughan

John Vaughan

John is a CPA, MBA and has been a Performance Management consultant for over 25 years. He is the founder of ExploringTM1 and highly regarded for his experience combining financial management with corporate planning, reporting and analysis. He lives in Sydney with his wife, two of his three children, their cat, Freckles, a bunch of chooks and some fish. John is a sports nut, who played rugby until he was 40, started playing football at 54 and loves being outdoors.

Leave a Reply

Your email address will not be published. Required fields are marked *

Log In