TM1 Forum

Hi,

I have created a timetable cube in TM1 and for the measure dimension which holds the date elements I have set these as simple. I then formatted them as b:dd mmmm yyyyD|0|Y

If I slice this cube out to excel and enter the date 23 March 2018 it ends up in TM1 as serial number 43182 but still shows as date 23 March 2018 in the TM1 cube.

But, for example, when I want to compare the date held in the timetable cube with say DayNo(Today(1)) I am having to subtract 21916 from the date in the cube.

I tried to DBSW the TM1 serial date for 23 March 2018 (so 43182 - 21916) but when I do this it shows as 23 March 1958 in the TM1 cube.

Is this normal behaviour that I to live with?

cheers, Mark

Mark,

There are several discussions on the board about this and I think Alan has an FAQ or Useful Tips discussion on date management between TM1 and Excel, so there is no use in repeating on this thread.

http://www.tm1forum.com/viewtopic.php?f ... 2A21916%2A

Hope the link provides some excellent information for you.

TM1s default date starts I think in 1960 whereas Microsoft is 1900 or something like that.
TM1 introduced a CFG a while ago called UseExcelSerialDate (I think) that will make the two align.

Hi,

Appreciate the feedback.

I have read Alan Kirks summary of date and times and I *think* I understand the difference between Excel and TM1.

What I couldn’t see in that summary or any of the comments was an explanation relating to my very specific issue, which I will restate.

Lets forget excel for a minute. In TM1 I have created a dimension called TimetableDates. I have an element within this dimension called TestDate and this is N property, i.e. simple. I then format the element attribute to b:dd mmmm yyyyD|0|Y.

I then create a cube which has this dimension as the last one.

When I put the number 43185 in the cube against TestDate it shows the date as 26 March 2018. I would expect 26 March 2018 to be number 21269 in TM1?

Am I missing something or does TM1 date format b:dd mmmm yyyyD|0|Y convert to excel dates within TM1 itself?

Apologies if I am missing something obvious here

cheers, Mark

What are you doing to drive the formatting?

Hello Thurston,

To change the formatting I am simply right clicking the dimension, edit element attributes and changing the format to date, then choosing the first option in the list of available date formats.

cheers, Mark