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.
Date calculation not working in Excel due to Windows date format
-
burnstripe
- Regular Participant
- Posts: 197
- Joined: Wed May 06, 2020 2:58 pm
- OLAP Product: Planning Analytics
- Version: 2.0.9
- Excel Version: 2016
Re: Date calculation not working in Excel due to Windows date format
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
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