Hi all,
When I use DayNo function to convert a date (e.g. YYYY-MM-DD) to a number to store in a measure. When formatting the measure to view the date in the cube viewer (DD-MM-YYYY), I do not get back the same date.
This is not consistent or coherent at all.
Is this a bug, or am I missing something? I'm using TM1 9.5.1.
Thanks.
Regards,
Melvin
Bug with Dates in TM1
-
melvinleng
- Posts: 13
- Joined: Mon Jul 11, 2011 3:07 am
- OLAP Product: TM1
- Version: 9.5.2
- Excel Version: 2007
-
David Usherwood
- Site Admin
- Posts: 1458
- Joined: Wed May 28, 2008 9:09 am
Re: Bug with Dates in TM1
My recollection is that dayno uses TM1's 'Day zero' of 31/12/1959, but the formats use Excel's Day zero of 31/12/1899, so you need to offset by the difference of 21916. Not sensible I know, but perfectly manageable.
-
John Hammond
- Community Contributor
- Posts: 300
- Joined: Mon Mar 23, 2009 10:50 am
- OLAP Product: PAW/PAX 2.0.72 Perspectives
- Version: TM1 Server 11.8.003
- Excel Version: 365 and 2016
- Location: South London
Re: Bug with Dates in TM1
He must have had a very good reason to chose a base date not compatible with the world most used business software.
31/12/1959 is Val Kilmer's birthdate so mebbe the TM1 founder liked that film, Top Secret, a lot.
31/12/1959 is Val Kilmer's birthdate so mebbe the TM1 founder liked that film, Top Secret, a lot.
-
Alan Kirk
- Site Admin
- Posts: 6647
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: Bug with Dates in TM1
David has probably pointed you in the right direction; I suggest reading this post if you want further clarification on that.melvinleng wrote:Hi all,
When I use DayNo function to convert a date (e.g. YYYY-MM-DD) to a number to store in a measure. When formatting the measure to view the date in the cube viewer (DD-MM-YYYY), I do not get back the same date.
This is not consistent or coherent at all.
Is this a bug, or am I missing something? I'm using TM1 9.5.1.
"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.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
melvinleng
- Posts: 13
- Joined: Mon Jul 11, 2011 3:07 am
- OLAP Product: TM1
- Version: 9.5.2
- Excel Version: 2007
Re: Bug with Dates in TM1
Thanks guys.
I guessed so. Just want to confirm this is the correct behavior. (Confusing though, when having a mix of formatting and using rules)
Melvin
I guessed so. Just want to confirm this is the correct behavior. (Confusing though, when having a mix of formatting and using rules)
Melvin
-
David Usherwood
- Site Admin
- Posts: 1458
- Joined: Wed May 28, 2008 9:09 am
Re: Bug with Dates in TM1
Via Stephen Waters, via an IBM gathering last week, the suggestion is that it relates to the Cuban Revolution. Yes, I know TM1 uses 31/12/59 and Castro rolled in on 1/1/59. I think Manny has more important things to sort out than this 
http://en.wikipedia.org/wiki/Cuban_Revolution
http://en.wikipedia.org/wiki/Cuban_Revolution
-
Carolyn
- Posts: 30
- Joined: Thu Jan 21, 2010 10:27 pm
- OLAP Product: TM1
- Version: 10.1
- Excel Version: 2010
- Location: Los Angeles, California
Re: Bug with Dates in TM1
I'm doing exactly that and it works great.David Usherwood wrote:My recollection is that dayno uses TM1's 'Day zero' of 31/12/1959, but the formats use Excel's Day zero of 31/12/1899, so you need to offset by the difference of 21916. Not sensible I know, but perfectly manageable.