Date held as number but formatted as date

Post Reply
Mark RMBC
Regular Participant
Posts: 154
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Date held as number but formatted as date

Post by Mark RMBC » Fri Mar 23, 2018 11:16 am

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

User avatar
PavoGa
Community Contributor
Posts: 206
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0
Excel Version: 2013
Location: Cleveland, Tennessee

Re: Date held as number but formatted as date

Post by PavoGa » Fri Mar 23, 2018 4:16 pm

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.
Ty
Cleveland, TN

declanr
MVP
Posts: 1587
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Date held as number but formatted as date

Post by declanr » Fri Mar 23, 2018 5:21 pm

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.

Mark RMBC
Regular Participant
Posts: 154
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Date held as number but formatted as date

Post by Mark RMBC » Mon Mar 26, 2018 9:48 am

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

Thurston Howell III
Posts: 25
Joined: Fri Mar 10, 2017 8:26 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2016

Re: Date held as number but formatted as date

Post by Thurston Howell III » Mon Mar 26, 2018 8:03 pm

What are you doing to drive the formatting?

Mark RMBC
Regular Participant
Posts: 154
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Date held as number but formatted as date

Post by Mark RMBC » Tue Mar 27, 2018 7:49 am

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

Post Reply