TM1 Date Display Using Attribute Format

Post Reply
jv_oz
Posts: 4
Joined: Fri Oct 13, 2017 12:44 pm
OLAP Product: TM1
Version: PA 2.0.2
Excel Version: 2016 64 bit

TM1 Date Display Using Attribute Format

Post by jv_oz » Sun Oct 15, 2017 11:48 pm

Hi all,

I want to store dates in a cube. I have a Simple element called "Variation Date" that then has date formatting applied to it via attributes using "c:dd-mmm-yy". I understand that TM1 dates start from 1-Jan-1960 and that the "real" value stored in this cell will be the numeric value associated with the date.

If I enter 13947 into the cell and refresh, it should display 09-Mar-98. It doesn't though! If I remove the formatting I can see 13947. When I add the formatting, either as "c:dd-mmm-yy" or one of the default formats, it displays 08-Mar-1938.

So what gives?

Note that I am not doing this in Perspectives, so the whole TM1/Excel date confusion should not be at play. But it's as if it is because 1998 (the year we expect to be displayed) is 38 years after 1960 (the start of TM1 dates), Excel dates start at in 1900 and we're getting 1938 displayed!

Any ideas are very welcome!

FYI, we have Planning Analytics 2.0.2 installed.

Thanks

JV

Edward Stuart
Community Contributor
Posts: 191
Joined: Tue Nov 01, 2011 10:31 am
OLAP Product: TM1
Version: All
Excel Version: All
Location: Manchester
Contact:

Re: TM1 Date Display Using Attribute Format

Post by Edward Stuart » Mon Oct 16, 2017 5:47 am

A good starting point is this comprehensive post on Dates and Times in the Useful Code section of the forum:

Working with Dates and Times

User avatar
Alan Kirk
Site Admin
Posts: 5724
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

Re: TM1 Date Display Using Attribute Format

Post by Alan Kirk » Mon Oct 16, 2017 8:19 am

There is something that I hadn't updated in the Dates And Times document, which applies to PA only; look for the "UseExcelSerialDate=T" config parameter in the Installation Guide. (I've added a cross-reference to this thread to the guide as an interim solution.)

If you look in your server config file, I'll bet that you have that set to True.
UseExcelSerialDate
Enables the use of Microsoft Excel serial dates instead of TM1 serial dates.

Parameter type: optional, static

When UseExcelSerialDate is enabled, TM1 rule functions and TurboIntegrator functions use Jan 1, 1900 as a base date for serial dates instead of Jan 1, 1960.
In the past, TM1 rule 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 TM1 expect cube data to use Microsoft Excel serial dates rather than TM1 serial dates.

To avoid the need to convert dates, enable UseExcelSerialDate to have rule functions use Microsoft Excel dates rather than legacy TM1 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 TM1 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 tm1s.cfg file and restart the TM1 server, you see the expected result, May 26, 2015, in TM1. The rule function is now using Jan 1, 1900 as the base date for serial dates.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

jv_oz
Posts: 4
Joined: Fri Oct 13, 2017 12:44 pm
OLAP Product: TM1
Version: PA 2.0.2
Excel Version: 2016 64 bit

Re: TM1 Date Display Using Attribute Format

Post by jv_oz » Mon Oct 16, 2017 7:35 pm

There is something that I hadn't updated in the Dates And Times document, which applies to PA only; look for the "UseExcelSerialDate=T" config parameter in the Installation Guide. (I've added a cross-reference to this thread to the guide as an interim solution.)

If you look in your server config file, I'll bet that you have that set to True.
Thanks Alan. I had thought that it would be something like that (didn't know about that specific parameter though). So I went to my config file and it was NOT there. Even after I explicitly added "UseExcelSerialDate=F", I am still getting dates that are behaving as if the start date is 1-Jan-1900. I think it might be time to hit IBM support up!

Thanks again!

JV

User avatar
Alan Kirk
Site Admin
Posts: 5724
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

Re: TM1 Date Display Using Attribute Format

Post by Alan Kirk » Mon Oct 16, 2017 7:55 pm

jv_oz wrote:
Mon Oct 16, 2017 7:35 pm
There is something that I hadn't updated in the Dates And Times document, which applies to PA only; look for the "UseExcelSerialDate=T" config parameter in the Installation Guide. (I've added a cross-reference to this thread to the guide as an interim solution.)

If you look in your server config file, I'll bet that you have that set to True.
Thanks Alan. I had thought that it would be something like that (didn't know about that specific parameter though). So I went to my config file and it was NOT there. Even after I explicitly added "UseExcelSerialDate=F", I am still getting dates that are behaving as if the start date is 1-Jan-1900. I think it might be time to hit IBM support up!
Not necessarily. In the TM1 Developer manual (page 14 of the .pdf) there is this interesting little snippet about Display Format Attributes:
Date Format:
Numbers appear as a date string. 1=January 1,1900. There are a number of date formats available.
When an element is formatted as Date, and the element is viewed in TM1 Web or TM1 Application Web, you can use a calendar date
selector to pick a new date value.
(My emphasis.)

No way that is going to be confusing, but it's a change that they had to move to. Indeed they should have bitten the bullet on this one long before this.

This whole thing came along about a year too late for me; up until last year I had one employee, just one, with a commencement date in the 1950's. It played merry havoc with the payroll cubes. It would have been even worse for anyone needing to use a date of birth.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

jv_oz
Posts: 4
Joined: Fri Oct 13, 2017 12:44 pm
OLAP Product: TM1
Version: PA 2.0.2
Excel Version: 2016 64 bit

Re: TM1 Date Display Using Attribute Format

Post by jv_oz » Mon Oct 16, 2017 11:14 pm

Wow. Ok, so thanks for that Alan. So if I am going to use date formatted cells, then so I can make the model work correctly it sounds like I should force the use of dates to use 1-Jan-1900. That way everything will be consistent. Would you agree?

User avatar
Alan Kirk
Site Admin
Posts: 5724
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

Re: TM1 Date Display Using Attribute Format

Post by Alan Kirk » Tue Oct 17, 2017 10:42 pm

jv_oz wrote:
Mon Oct 16, 2017 11:14 pm
Wow. Ok, so thanks for that Alan. So if I am going to use date formatted cells, then so I can make the model work correctly it sounds like I should force the use of dates to use 1-Jan-1900. That way everything will be consistent. Would you agree?
I think in the long term it'll make life easier, though I haven't yet checked (since I don't have 11 / PA2 installed yet) whether IBM has taken into account the "Year 1.9K bug" (the incorrect treatment of 1900 as a leap year). I imagine that they have or someone would have noticed the difference before now.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

jv_oz
Posts: 4
Joined: Fri Oct 13, 2017 12:44 pm
OLAP Product: TM1
Version: PA 2.0.2
Excel Version: 2016 64 bit

Re: TM1 Date Display Using Attribute Format

Post by jv_oz » Wed Oct 18, 2017 3:48 am

Cool. Thanks Alan.

Post Reply