TM1 Forum

I have below query:

I am fetching date using SUBNM in an Excel sheet.
Cell A2 = 26-01-2024
This date is in format DD-MM-YYYY.
There is further excel calculation.
For example, Cell B2 = A2+3
When the windows date format is the same, Cell B2 is calculating fine.

However, when the windows format changes to YYYY-MM-DD, Cell B2 is giving #VALUE error.

Client has to manually change the windows date format everytime he uses this report. Is there any way in TM1 Excel or VBA where we can make this work?

Thank You in advance.

Do you have a serial number stored as an alias to the date?
If you did you could then have pull this serial into the excel report and then perform the further excel calc B2 = A2 + 3 substituting A2 with the new serial number.

As an aside, were you aware of a date picker
https://community.ibm.com/community/use ... or-paw-cub

There is also an server configuration that can be made to make the serial number used for dates in tm1 synced up with those in excel
https://www.ibm.com/docs/ru/planning-an ... serialdate