TM1 Forum

Hi,
I need to calculate the number of days between 2 given dates in a TM1 Websheet. The dates come with a string format, which usually will be handled in Excel (2003) with the DATEVALUE function in order to process numeric calculations. Actually it works fine in the Excel environment, however, in Web it causes a #VALUE error.

TM1Web documentation says that DATEVALUE is a supported function.
I even tried some various cell formatting (date/text/value), but did not succeed either.

Is there anybody out here to recommend a solution for the problem described above, either with the DATEVALUE approach or not?

Thanks for you help!
Helmar

Raise an SR. If datevalue is supported it should work.

Helmar wrote:Hi,
I need to calculate the number of days between 2 given dates in a TM1 Websheet. The dates come with a string format, which usually will be handled in Excel (2003) with the DATEVALUE function in order to process numeric calculations. Actually it works fine in the Excel environment, however, in Web it causes a #VALUE error.

TM1Web documentation says that DATEVALUE is a supported function.
I even tried some various cell formatting (date/text/value), but did not succeed either.

Is there anybody out here to recommend a solution for the problem described above, either with the DATEVALUE approach or not?

I agree with David, this is one for a support request. According to the Supported Functions list it should work, and it does kinda sorta work, but not "as advertised". Mine is also 9.1SP4, and what I found is:
Date string is in the format DD-Mmm-YY : Works.
Date string is in the format DD/MM/YYYY, and yes, it IS a string, has the single quote mark in front of it and everything : Does not work, returns #Value

But here's the kicker...
Date stored in the cell as a date in the format dd/mm/yyyy... returns the correct DateValue value, even though in Excel that will generate a #Value error.

Thanks a lot for your instant reply. It's been my first inquiry on this platform, and I really appreciate the community's expertise.

However, I am still struggling with the problem. I did not manage to set the right format needed in TM1Web so far. It seems that it's not a problem with 'true' date functions such as TODAY(), but with the comparative dates, which are gained from a text attribute of a dimensional element.

Anyway - I am now thinking of a wholly new approach, which avoids date functions at all.

Thanks again & kind regards!
Helmar

I am attempting to do some date validation in a TM1 websheet. I decided to use the excel DATEVALUE function to test if the date is a valid number and if not return Invalid date.

This works fine in excel, perspectives, but on the web all sorts of bugs appear depending on format etc.

When using the following,
$K$9 being for example 27/07/16

Nothing is showing as invalid on the web because it returns a value even for invalid dates, for example if I use 29/02/17, in perspectives this correctly shows as an Invalid Date.

I am assuming this is related to being on TM1 version 10.1?

However if I replace DATEVALUE with the function VALUE it appears everything works correctly, so I now have the following,

=IF(ISERROR(VALUE($K$9)),"Invalid date","")

Before I proceed with using VALUE instead of DATEVALUE can anyone spot any potential issues here?

Cheers, Mark