Cells containing strings that start with digits are treated as numbers

[deftdawg]

EDIT: search for "beers" example in follow up comments below, this issue is more widespread than just dates.

Any chance that we can have quotes added when they are missing? Feels kind of silly putting quotes around date values in a column.

Week	Date
\<!--FM WEEKNUM(B1)--> 3	"2022-01-10"	Ok
\<!--FM WEEKNUM(B2)--> 28	2022-01-10	<-- Shouldn't be week 28
\<!--FM WEEKNUM(B3)--> 24	"2022-06-07"	Ok

[oswida]

We need to make some kind of assumption here. To determine proper types for functions use, parser tries to find out if the cell value is a number. Unfortunately, Number.parse works correctly with the strings like '2022-01-10', so this is why it is parsed unproperly. To satisfy the requirement I need to recognize if a given string is a date or a number, maybe I can find some library for that, but still dates will have to be put in a proper format anyway. Unfortunately again, Date.parse(1984) gives a proper result too.

[deftdawg]

Can we assume something like a trimmed value matching something like ^-?\n+(\.\n+)? is a number and everything else is a string? Maybe it would be easier

[oswida]

There are multiple formats for a number, what about locale dependent or scientific notation? Thinking about it, it becomes more and more reasonable to require these quotes - that way the user can state clearly, that he wants this value to be treated as a date.

[deftdawg]

Can't say I agree that it makes sense, it's certainly not intuitive since users don't do that in excel... There should be a straightforward coding solution to dealing with numbers vs not-numbers.

Googling I found isNaN(), seems that will return true for anything that is not a number, I'd be surprised if formulajs accepted anything other than values that pass !isNaN(value) == true. So if isNaN(value), we could pass with quotes.

[oswida]

If you look into the code, you will certainly find out that I've implemented isNaN check for Numbar.parseFloat long time ago. And no, parseFloat is not returning isNaN for 2022-01-10 (even if you put it as a string argument) - this is exactly the cause why you have encountered described problem.

[deftdawg]

Yep, I guess I'll have to take a look at the code.

This also doesn't quite work because it looks like any string that starts with a number is treated as a number rather than string (see C3 below).

Base	Addtion	Result	Note
Ten beers	is a lot	\ Ten beers is a lot	Good
Having 10 beers	is a lot	\ Having 10 beers is a lot	Good
10 beers	is too much	\ 10 is too much	BAD: "beers" goes missing
"10 beers"	"is too much"	\ "10 beers" "is too much"	Should be "10 beers is too much"
"10 beers"	is too much	\ "10 beers" is too much	We got "\"10 beers\"" added to the 2nd string
"10 beers"	is too much	\ #VALUE!	Ok
10x beers	is too much	\ 10 is too much	BAD: Same as C3
10.5 beers	is too much	\ 10.5 is too much	BAD: Same as C3

[deftdawg]

Meh, even if I figure out how to fix this number handling bug, it looks like my dream of using this for time calculations is toasted because the difference between how DATEVALUE as it is done with formula JS and how it's handled by this plugin.

Source	Method	Value	Note
Markdown Calc	\	40778	This is an Excel Date (Days since Jan 1 1900) - Time info is lost 😢
Formula JS	DATEVALUE('8/22/2011') * 1	1313985600000	/1000 = Unix epoch time i.e. gdate -d @1313985600 = Mon Aug 22 00:00:00 EDT 2011

This formula works in Formula JS, but it's hopeless without time data...

((DATEVALUE(REGEXREPLACE(DATEVALUE("2022-09-07")+"","\\d\\d:\\d\\d:\\d\\d","5:30 PM")) - DATEVALUE(REGEXREPLACE(DATEVALUE("2022-09-07")+"","\\d\\d:\\d\\d:\\d\\d","8:00 AM")))/3600000)

(result is "9.5" hours)

[oswida]

I do not see any reasonable way to fulfill such a requirement.