Forcing cell to recognize a date


I am initializing some cells with a customized date value, converted as a string, such as dd/mm/yyyy. This is received as a string to Excel. When I sort the column, it sorts it as a string and not as a column date recognized.

How can I achieve this column date recognition but still preserving my string cell initialization as I need to pass various date formats and if I initialize the cell directly with the data, I will not be able to obtain that.


If you enter strings, cells will of course be recognized as strings. The only way to have this sort fine is to use a "yyyy-mm-dd" format.

But I am not sure I understand why you don't want to enter the dates as dates? A real date in the cell will work much better than a string (you can also use it in formulas for example), and you can format the cell in any way you like.

Can you expand a little on why you can't enter real dates in the cells?


This is because, based on the user, the report is generated in a certain way. So, they have different date format. Also, some columns are shown as dd/mm/yyyy, some others as dd/mm/yyyy hh:mm, some others as dd/mm/yyyy hh:mm:ss. Other variations could be mm/dd/yyyy, mm/dd/yyyy hh:mm, even yyyy just to show the year or mmyyyy just to show the month and year without special characters and so on.

So, yes, I do have to pass this as a string. Should I simply tell the client that there is no way to have it both ways?

I still don't see the reason you can't enter this as a date, and then dynamically format the cell as mm/dd/yyyy, mm/dd/yyyy hh:mm, dd/mm/yyyy, etc. Everythig you are doing can be done by dynamically formatting the cells. You can even have a special mode (formats marked with *) which will change depending on the locale, if you want that. So an english user would see "mm/dd/yyyy" and an spanish user "dd/mm/yyyy" when opening the same file.

Let me know if you are using FlexCelReport or XlsFile, and also if Delphi or .NET. I'll send you an example on how dynamically format the cells.


We cannot use the local settings. This is dependent on the Web site user account setting.

I am using XlsFile in .NET.

It is good to know there is a way. I will wait for your input.


Ok, I found what was causing this effect in my code. The support was already there but it as skipping it.