TM1 Forum

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

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.

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.

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.

David has probably pointed you in the right direction; I suggest reading this post if you want further clarification on that.

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

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

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.

I'm doing exactly that and it works great.