I need to format the column to a specific format.
I tried this :
// Set format date
colStyle = mySheet.Cells.Columns[colIndex].Style;
colStyle.Number = 14;
colStyle.Custom = “mm/dd/yyyy”;
The bug is, my client is UK. When he types in a date inside the formatted column for example: 20/04/2001 then excel converts it to 04/20/2001
meaning that the text and the value are different.
But if the client enters 04/20/2001, it seems that the cell is not well-formatted even if the format style is custom dd/mm/yyyy.
2 tests to make:
- check that if you enter 02/25/2007 in the cell, it’s really the value entered there (check that the value equals the text of the cell).
- if you drag the cell corner to the cells below it should extend a range of dates like this:
In my scenario, the client has U.K regional setting (which is not MM/DD/YYYY but DD/MM/YYYY)
I wish that its regional settings wouldn’t infer in my formatting choice.