This particular Date is causing problems

[Lolailo]

So our clients have to turn in Excels with Date fields among others, when loading one of these into my program suddenly my IDE throws an exception. I debug and find out that a particular cell on a Date column is responsible: 22/10/2014.

The cell is formatted the same as all other date cells, and the actual value doesn't (apparently) differ in format from the others in the same Excel, but somehow my program can't format it to 22-oct-2014, it will fetch 22/10/2014 literally as a String, leading to a crash. Too big of a thing to control the issue inside the logic.

The question would be, IS THERE ANY CHAR IN A KEYBOARD THAT COULD MAKE MY PROGRAM NOT TO INTERPRET THE VALUE AS A DATE, BUT STILL OVERCOME EXCEL'S CELL VALIDATION? If not, please you are welcome to throw in some ideas.

Thank you for the support.

[kev_]

see attached workbook which may help you identify the character
- formulas =CHAR and =CODE applied to discover cell contents
- it is not perfect (leading apostrophe ' is ignored by =CODE )
- see A1 which begins with apostrophe space apostrophe
- but B1 thinks that the space is the first character
- even LEN(A1) ignores it and tells us that there are 12 characters when there are 13
- also notice that if we concatenate the characters from a cell which contains a valid date we get a date value (see formula in cell M6)

You could edit the "bad" cell in the Formula Bar and clear anything before the day value
- there may be a "big" space or a "small" space character or an apostrophe (or something else!)
- the space may not be visible but you can delete it if you put the cursor in the correct place