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
Date held as number but formatted as date
- PavoGa
- MVP
- Posts: 617
- Joined: Thu Apr 18, 2013 6:59 pm
- OLAP Product: TM1
- Version: 10.2.2 FP7, PA2.0.9.1
- Excel Version: 2013 PAW
- Location: Charleston, Tennessee
Re: Date held as number but formatted as date
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.
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
Cleveland, TN
-
- MVP
- Posts: 1815
- 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
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.
TM1 introduced a CFG a while ago called UseExcelSerialDate (I think) that will make the two align.
Declan Rodger
-
- Community Contributor
- Posts: 292
- 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
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
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
-
- Posts: 36
- Joined: Fri Mar 10, 2017 8:26 pm
- OLAP Product: TM1
- Version: PA 2.0.8
- Excel Version: 2016
- Location: Dallas, TX
Re: Date held as number but formatted as date
What are you doing to drive the formatting?
-
- Community Contributor
- Posts: 292
- 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
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
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