Using Date value in formula Doesn't give #value error

Tags: #<Tag:0x00007f0b099bd708> #<Tag:0x00007f0b099bd320>

Hi folks
By using hands-on table v12.1.0 we have a date column and whenever we are referring to a date column value in the formula it is calculated instead of giving #VALUE error
So firstly I want to understand How come date value is evaluated in the formula and why it is different from Excel (which gives errors when date value is used in the formula)?

LINK: https://jsfiddle.net/gya2p3dx/
Thanks

Hi @siddhantjain15298

In you example the date type column isn’t in the range of the formulas calculation. But it’s true that the values are calculated byt the engine even if they are of date type. However, I checked similar solutions in Google Sheets and Excel, and in both cases they will also be calculated and don’t throw the error.

We consider it to be a feature, If you look at this example: https://jsfiddle.net/handsoncode/kdzo5u0p/ you can easily do calculations on date cell types. In this case the value from column B is increasing the Day value in calculated formula. You can also read about date and time support in HyperFormula here: https://hyperformula.handsontable.com/guide/date-and-time-handling.html#moment-js-integration

Hi @adrian.szymanski
I try to calculate date value into formula which gives following output ‘########’
MicrosoftTeams-image%20(5)
can u explain it more how it is working on excel and google sheet
Thanks

@siddhantjain15298

What formula are you using to get ####### ?

image

I also tried SUM() which worked fine in google sheets as well. Or is it only using HyperFormula that you get no value?

Hi @rene.sinnbeck
Handsontable and hyperformula working fine while handling date values into formula
But I think I am not able to replicate same behaviour in excel like I tried ‘=A2*2’ in my example mention above

Yeah it seems that hyperformula treat the date as a number while google docs treats is as a date (and adds days to the date)

@rene.sinnbeck

Yes, that’s the case here. In HyperFormula the date is interpreted as a number.