Ha ha, TM1 does indeed predate Excel by a couple years!
I thankfully have limited cases of this personally and do the necessary handling in TI.
Using a calculated member in MDX may help Asgeir, although this would be a read-only member so does not address any selection/updates.
The MDX could add 21915 to the value in the underlying cell and then format it.
WITH MEMBER
[_S-Assumption].[_S-Assumption].[Now plus 21915] AS
[_S-Assumption].[_S-Assumption].[Current Time]+21915.0,
SOLVE_ORDER = 1, FORMAT_STRING = '#,##0.00;(#,##0.00)'
MEMBER
[_S-Assumption].[_S-Assumption].[Now plus 21915 formatted] AS
[_S-Assumption].[_S-Assumption].[Current Time]+21915.0,
SOLVE_ORDER = 2, FORMAT_STRING = 'yyyy\/mm\/dd'
SELECT
{[_S-Assumption].[_S-Assumption].[Current Time],
[_S-Assumption].[_S-Assumption].[Now plus 21915],
[_S-Assumption].[_S-Assumption].[Now plus 21915 formatted]} ON 0,
{[_S-Assumption Measures].[_S-Assumption Measures].[ValueN]} ON 1
FROM [_S-Assumption]
WHERE ([Company Code].[Company Code].[# Company Code])
The resultant view would be something like the below:
I did need to update the format on PAW to "Use database format" before it actually rendered based on the MDX format.
Also, I did not need two members, I included the "Now plus 21915" purely as an intermediate measure for clarity.
@Asgeir Thorgeirsson - you have some options...
------------------------------
George Tonkin
Business Partner
MCI Consultants
Johannesburg
------------------------------
Original Message:
Sent: Fri April 12, 2024 08:49 AM
From: Hubert Heijkers
Subject: Seeking Advice on Date and Time Display Formats
Hi George,
Given the fact that TM1 predates Excel I'd argue we'd have to log this with Microsoft <LOL>
Kidding aside, it's annoyed me in the past as well but my suggestion to change this couldn't count on much support as it would potentially be breaking customers in ways we can predict (not knowing how people use it). But it is the customer that assigns a meaning to an otherwise numeric value in a cube.
Open for suggestions to 'fix' this, short of me introducing types to tackle this once and for all ;-)
Cheers,
-H
PS wouldn't mind the ticket to spike the discussion on the topic once again (and otherwise make at least support aware of this magic 21915 value, which I'm sure they'd find in more support case;-).
------------------------------
Hubert Heijkers
STSM, Program Director TM1 Functional Database Technology and OData Evangelist
Original Message:
Sent: Fri April 12, 2024 01:21 AM
From: George Tonkin
Subject: Seeking Advice on Date and Time Display Formats
Hi Asgeir,
If the behaviour is different, I would suggest you log this with IBM as they may not be aware.
The functionality should work consistently, whether it works consistently as you need it or not will be the next issue.
------------------------------
George Tonkin
Business Partner
MCI Consultants
Johannesburg
Original Message:
Sent: Thu April 11, 2024 01:27 PM
From: Asgeir Thorgeirsson
Subject: Seeking Advice on Date and Time Display Formats
Thank you @George Tonkin
The mind-boggling part is that when you select a date with the date selector or type in a date in PAW, it is stored with the 1900 base. So you also have to subtract 21915 from the selected date if you want to work with that date in TM1 rules or a TI process. So I am saying there is a conflict within TM1/PAW because it uses both the 1900 base and the 1960 base as default. Am I right or is this a misunderstanding?
Cheers, Asgier
------------------------------
Asgeir Thorgeirsson
Original Message:
Sent: Thu April 11, 2024 10:49 AM
From: Asgeir Thorgeirsson
Subject: Seeking Advice on Date and Time Display Formats
Hey everyone,
I could use some help with date and time display formats in PAW, specifically when working with the NOW() function. Here's the situation:
-
NOW() Function Usage: In one of my TI processes, I'm using NOW() to capture the numerical date/time and storing it in a TM1 dimension member. For example:
ATTRPUTN(NOW, 'Import_Log', Import_Log, 'EndTimeN')
-
Display Format Issue: Following that, I'm attempting to set the display format (within PAW) for the date and time. I've set the format for 'EndTimeN' like this: d:m/d/yyyy h:nn:ss ID|DT|S|M|Y|Y.
Just to note, the format string includes a special character after 'h:nn:ss'. 
However, despite this setup, I'm encountering a problem.
Instead of displaying something like 10.04.2024 13:52:46, I'm getting 10.04.1964 13:52:46
when the numerical date is 23477.57831018519.
Seeking Advice: Any suggestions on how to resolve this discrepancy in the date and time display format?
Additional Info: I've tried consulting the IBM manual for Date and Time display formats (link), but without success. While I did receive some help experimenting with date and time display formats in the Performance Modeller, I generally don't use that product.
I'd appreciate any insights or suggestions you might have. Thanks for your assistance!
Cheers, Asgeir
------------------------------
Asgeir Thorgeirsson
------------------------------