TM1 Forum

Hi!

I'm having an issue that really annoys me regarding Excel date conversion to number.
1 - I have a cube where several date measures can be entered using String elements
2 - I have an Excel input form based on a view of that previous cube and DBRW formulas

When I try to enter a date on a DBRW formula, it sends the numeric value of the date instead.
When I change the format of the cells to text, I get a "Formula error" then a "TM1 Error" from Perspectives and the text of formula itself is sent to the cube (with L1C1 style reference which is not what I use).

I know a workaround consists in prepending the character ' but is there any other solution?

Thanks!

Just as a matter of interest, why is it that you prefer the dates to be sent as strings, particularly as they could be in any of a number of formats? Excel date numbers are ideal for date formatted cells in TM1 and the conversion to proper date strings in your format of choice is easy (see the definitive post on this here, which incidentally at the end has a possible solution for you).

I agree with Duncan here. I tried storing dates as strings earlier in my career, but I don't think there was a single case where I didn't end up converting it to a date serial for one reason or another.

I believe it's much more efficient to convert from a serial to a date string, so I pretty much without exception store dates as numbers now.

The only exception to this was when the first version of Contributor came out, and it had that annoying bug that it couldn't take a date string and convert it to a number like Excel does. I was forced to allow input as a string, but it was much less efficient and caused a lot of additional pain in the rules.

Just remember to add or subtract 21916 as appropriate!

Thanks for your replies. I wanted to store date as string because I have to import dates from another system into TM1 and I just wanted to avoid multiple conversions. I guess I have no choice here