Just to share, I've just encountered the same thing few minutes ago. It seem there is a 3rd way to overcome this, which is by inputting the date in YYYY-MMM format. It seem inputting date format starting with YYYY, excel will not convert it to number.tonester30 wrote:For instance in the Web when the user enters "01/01/2010" (US format) the format held in TM1 as originally input, but now when the user enteres "01/01/2010" in Excel it saves as "40179" in TM1.
Using Dates And Times In TM1
-
- Posts: 58
- Joined: Wed Sep 14, 2011 3:10 am
- OLAP Product: TM1 Cognos Xcelerator
- Version: 9.4
- Excel Version: Office 2003
Re: Using Dates And Times In TM1
Hi all,
-
- Posts: 132
- Joined: Tue Sep 06, 2016 7:55 am
- OLAP Product: TM1
- Version: 10.1.1
- Excel Version: Excel 2010
Re: Using Dates And Times In TM1
Hi,
Just want to confirm something about dates in TM1.
I have a TI process that includes a couple of parameters for start and end date.
On the web the user inputs a start and end date.
I was hoping I could use the DayNo function to test if the dates were valid and processquit if they weren’t. However I notice that if I put in an invalid date, such as 29 February 2018 the dayno function returns the serial number for the 28 February 2018, rather than throwing up an error.
So it seems TM1 does some conversion with dates. Can anyone verify this and are there other examples of such conversions in TM1?
Incidentally I got round the problem of validating the dates by validating them on the websheet, so I have a new parameter, pValidDate and if a cell on the websheet shows invalid the process errros saying the dates are invalid!
cheers, Mark
Just want to confirm something about dates in TM1.
I have a TI process that includes a couple of parameters for start and end date.
On the web the user inputs a start and end date.
I was hoping I could use the DayNo function to test if the dates were valid and processquit if they weren’t. However I notice that if I put in an invalid date, such as 29 February 2018 the dayno function returns the serial number for the 28 February 2018, rather than throwing up an error.
So it seems TM1 does some conversion with dates. Can anyone verify this and are there other examples of such conversions in TM1?
Incidentally I got round the problem of validating the dates by validating them on the websheet, so I have a new parameter, pValidDate and if a cell on the websheet shows invalid the process errros saying the dates are invalid!
cheers, Mark
-
- MVP
- Posts: 1561
- Joined: Mon Dec 05, 2011 11:51 am
- OLAP Product: Cognos TM1
- Version: PA2.0 and most of the old ones
- Excel Version: All of em
- Location: Manchester, United Kingdom
- Contact:
Re: Using Dates And Times In TM1
You could just have them input to a numeric cell that is formatted as a date. In older versions it just wont allow something that isn't a date, as of 10.2.2 (or maybe PA) it actually brings up a date picker in the web... and obviously still as before won't allow a "non" date to be entered.
Declan Rodger
http://www.spitfire-analytics.com
http://www.spitfire-analytics.com
-
- Posts: 132
- Joined: Tue Sep 06, 2016 7:55 am
- OLAP Product: TM1
- Version: 10.1.1
- Excel Version: Excel 2010
Re: Using Dates And Times In TM1
Hi Declan,
Thanks for the response. Apologies if I am being dim.
Do you mean create 2 numeric measures in tm1 and format these as dates?
If so I am not holding the start and end dates in TM1 I am just using the start and end date to drive some logic in the TI process. So these dates only exist as string parameters in the TI process.
cheers, Mark
Thanks for the response. Apologies if I am being dim.
Do you mean create 2 numeric measures in tm1 and format these as dates?
If so I am not holding the start and end dates in TM1 I am just using the start and end date to drive some logic in the TI process. So these dates only exist as string parameters in the TI process.
cheers, Mark
-
- MVP
- Posts: 1561
- Joined: Mon Dec 05, 2011 11:51 am
- OLAP Product: Cognos TM1
- Version: PA2.0 and most of the old ones
- Excel Version: All of em
- Location: Manchester, United Kingdom
- Contact:
Re: Using Dates And Times In TM1
I did mean that - you would format them using the element attributes.
In these cases I normally add attributes against the }Clients dimension and use those but that is only personal preference so I have some extra audit control over who has input what etc.
But failing that for PA at least you can have a standard excel cell (no TM1 formula) formatted as a date (excel formatting) and that will bring up the date picker in TM1 Web and still have restriction in place to make sure you don't type a stupid date.
Declan Rodger
http://www.spitfire-analytics.com
http://www.spitfire-analytics.com