1

I need to reliably determine if a user inputted string is a date or a number.

Consider the string 1.1. This string successfully parses to a double using

double.TryParse(s, NumberStyles.Float | NumberStyles.AllowThousands, CultureInfo.CurrentCulture, out var result)

This string also successfully parses to DateTime using

 DateTime.TryParse(s, out var dResult)

We need this string to be interpreted as a double in English.

More complexity comes when you consider other cultures.

In German for example the NumberGroupSeperator and the DateSeperator are both dots, rather than a comma and slash, as in English. So in German, the string 1.1 should actually parse to a DateTime (ie: January 1 of the current year). The only way to know this programatically is to validate the NumberGroupSeperator and the NumberGroupSizes (because the string 1.1 in German is equivalent to 1,1 in English, which we know should not be parsed to a double or integer because the NumberGroupSeperator (comma) is in the wrong place).

From reading documentation (and this super passive aggressive GitHub issue conversation) there is no way for .NET to actually validate the NumberGroupSeperator and NumberGroupSizes when parsing.

Has anyone encountered this issue before and how did you solve it?

I wrote some code to manually validate the NumberGroupSeperator and NumberGroupSizes, but it seems like there should be a better way. I can share this code if anyone is interested.

EDIT

For those of you asking for more information about the business problem. I'm working on an open source project that allows users to edit Excel spreadsheets via a c# API. Consider someone entering the string "1.1" into cell A1 and the string "1" into cell B1 and then uses this open source library to try to calculate the formula =A1+B1 in another cell. This project needs to parity Microsoft Excel exactly. So if we're in a language/culture where "1.1" is interpreted as a date, then the result of this addition should be the OADate of Jan 1, 2018 plus one (ie: 43101 + 1 = 43102). But if we're in a language where "1.1" is the number 1.1 then the result of this calculation should be 1.1 + 1 = 2.1.

And yes, we do know the user's language/culture when doing this calculation, but the solution needs to work in all cultures.

Jaskier
  • 1,075
  • 1
  • 10
  • 33
Shawn Crabtree
  • 21
  • 1
  • 5
  • 5
    This sounds like an [XY problem](https://meta.stackexchange.com/q/66377/386424). What is your end goal? Why are you trying to do this? – Igor Oct 18 '18 at 19:34
  • 1
    What is the business scenario? Is it user input you need to validate? – Rui Jarimba Oct 18 '18 at 19:35
  • 3
    The standard way of solving this is specifying unambiguously how dates and number are to be represented and rejecting any input that does not follow the specification. Doing anything other than that and you may as well just generate random numbers for your data. – Dour High Arch Oct 18 '18 at 19:36
  • 1
    Sounds like a Vexing Exception. I highly recommend reading Eric Lipperts Blog - [Vexing exceptions](https://blogs.msdn.microsoft.com/ericlippert/2008/09/10/vexing-exceptions/), I think it will shed light on reasons. – Erik Philips Oct 18 '18 at 19:37
  • Spot on @DourHighArch – Rui Jarimba Oct 18 '18 at 19:40
  • 2
    _You have no idea the can of worms you've just opened._ Pretty much **no one** does this, because the problem space is so deceptively large. You need _something_ to help limit your inputs or provide context for the field. – Joel Coehoorn Oct 18 '18 at 19:41
  • "More complexity comes when you consider other cultures. " You either leave the picking of the Culture Format to the Parse/ToString Funcitons - wich automatically querries the Users Settings to get it. Or you pick a single fixed culture to be used at all endpoints. anything else will just not work. That path lies madness and no easy answer: https://www.youtube.com/watch?v=0j74jcxSunY – Christopher Oct 18 '18 at 19:43
  • See the edit for more information about the bussiness problem. – Shawn Crabtree Oct 18 '18 at 20:06
  • 1
    `This project needs to parity Microsoft Excel exactly` Then your project should run Excel behind the scenes to do the work. I know this sounds crazy, but really it is the main viable option. – mjwills Oct 18 '18 at 20:35
  • 1
    `Consider someone entering the string "1.1" into cell A1 and the string "1" into cell B1 and then uses this open source library to try to calculate the formula =A1+B1 in another cell` - if you do this, by default Excel will sum the 2 values (assuming the decimal separator is `.`) and display the result as a number. I think the only way for the result cell to be displayed as a date is to format the cell (let's say C1) as a date – Rui Jarimba Oct 18 '18 at 20:47

1 Answers1

0

I believe your current approach (formalizing your logical requirements and precedence rules in a custom parser) is the correct solution if you need C# to behave like Excel. C# is not excel, and has no built-in requirement or logic to make it behave in a similar way.

My two cents: If I was writing a C# API to work with Excel data, instead of following Excel's parsing rules, I would follow C#'s parsing rules (or more stringent ones with some of your logic applied where appropriate) and only write the data to the excel file. This avoids trying to mimic an uncontrolled system, and causes no harm to the correctness of the resultant workbook.

Andrew Hanlon
  • 7,271
  • 4
  • 33
  • 53