Using Dates And Times In TM1

[Misty]

Hi there,

Sorry if I'm missing something, but I'm having a problem with a string cell in TM1, where if a user types '01/07/2010', the immediate write-back changes it to 7 January 2010'. I'm not sure which setting I should fiddle with, as I'm pretty sure the server would be set to Australian format, not American.

Any ideas?

Thanks

[Alan Kirk]

Sorry if I'm missing something, but I'm having a problem with a string cell in TM1, where if a user types '01/07/2010', the immediate write-back changes it to 7 January 2010'. I'm not sure which setting I should fiddle with, as I'm pretty sure the server would be set to Australian format, not American.

Admin note: This isn't exactly a "hint or tip", and has therefore been split off from a Hints and Tips thread into a normal question in the main forum.

This probably has nothing whatever to do with TM1. Chances are that it's Excel doing a standard conversion. When you enter something that looks like a date in Excel, Excel will treat it as a date and show it in the format that you have specified for the worksheet. This issue was addressed in the later messages of the thread that you originally posted to.

As I mentioned there,

Excel has a few incredibly irritating habits, one of which is that if an input looks like a date, Excel will "help" you by converting it to a date before storing it in the cell.

And as I mentioned further down:

House-train the users to prefix their entry with a single quote mark. If Excel receives an input of '01/01/10 instead of just 01/01/10, it'll know that you mean text and won't do the pre-storage conversion. Consequently the text will go into the cube cell "as is".