Disregard locale indicators in number format

[mboeh]

We had a report of certain spreadsheets not being read correctly by our application. Investigation showed that depending on the date/time format, cells containing a date were sometimes being returned as fractional days.

We determined that the number format for these cells included the string "[$-409]" before the datetime formatting. Cells are checked to see if they are numbers before anything else, and since the presence of "0" causes the :number regex to match, the datetime checks never occur.

"[$-nnn]" is Excel formatting code that controls which locale is used to render the date. It's not relevant to determine the type of data in the field, so we are just removing it from the string before testing it with the regex.

As far as we can tell, this format was produced by Excel itself. Selecting Format -> Cells... -> Date and then a format in another locale will reliably generate the "[$-nnn]" code.

[zdavatz]

Thank you! Version 1.1.0 is out.

[zdavatz]

Something of both. Are you not happy with it?

[pacoguzman]

Hi guys, we're stuck on v1.0.4 because on v1.0.5 the number format check was introduced and this still doesn't solve or problems. We basically have two problems:

1 - Some of the locales that we found in our customer spreadsheets has the form [$-F800] what I'd found googling is that can be an hexadecimal number so I think we need to improve the regexp. I can do that. 2 - We're using a custom format for durations "hh:mm:ss.000" that now is taken as a number instead as fractional days as before (Datetime object). We had custom code after spreadsheet execute its own but spreadsheet behaviour changed with this custom format. I think the number regexp has to check something else not just a 0

What do you think? thanks in advance

[zdavatz]

thank you for reporting this. Lets hope for a great solution for all the issues.