GetCellValue of a Date cell returns incorrect Excel Serial Date number

[jeffrod1]

Hello,

I am not sure if this is a bug so I wanted to ask the question in case I am doing something wrong. I am simply checking a cell's value. I know that for a cell formatted as a Date, it will return the Excel Date Serial Number (ex. 45012). When checking the value, I am getting a serial number with decimals. I have put it the values below. 2023-03-27 should have a value of 45012 without decimals. I am thinking maybe the value is taking my timezone into account? Please assist. Thank you for your time.

Serialized B13: 2023-03-27T00:00:00.000Z
Value B13: 45011.8333333333

[AMBudnik]

Hi @jeffrod1

Could you share your code sample? When I use DATE() function on =DATE(2023, 3, 27) the value I get is 45012 in hf.getCellValue(). This seems to be aligned with what we have at https://hyperformula.handsontable.com/guide/built-in-functions.html#list-of-available-functions based on the default nulldate being {year: 1899, month: 12, day: 30}.

[jeffrod1]

Hello,

According to the docs for Excel compatibility, nullDate: { year: 1899, month: 12, day: 31 },. That is what I was using. I am not sure if that is part of my problem. Either way, the date seems to have a decimal value on the end of it (45012.8333333333). Since I am not specifying time, I am not sure where this is coming from.

I am using a slightly modified version of the File Import sample code in the HyperFormula guide. I am using ExcelJS and am importing that spreadsheet into HyperFormula. I have attached a zip of the code and a sample spreadsheet that shows the issue.
sample_code.zip

Thank you for your time!

[jeffrod1]

I also just realized the date 45011.8333333333 = 2023-03-26 8:00PM Eastern Time = 2023-03-27T00:00:00.000Z which is my local time zone, if that helps at all. I am not sure what, if anything, could be affecting that but it was something I just realized.

[AMBudnik]

Thank you for spotting that @jeffrod1

@sequba is there are reason for the 1 day gap between https://hyperformula.handsontable.com/api/classes/config.html#nulldate and https://hyperformula.handsontable.com/guide/compatibility-with-microsoft-excel.html#full-configuration

[sequba]

@jeffrod1 Sorry for such a late reply.

I looked at sample_code.zip that you posted, but there is a lot of code there and it's hard to analyze. Please create a minimal example that reproduces this bug. A time zone issue might be the cause of it.

[sequba]

@AMBudnik

@sequba is there are reason for the 1 day gap between https://hyperformula.handsontable.com/api/classes/config.html#nulldate and https://hyperformula.handsontable.com/guide/compatibility-with-microsoft-excel.html#full-configuration

Yes, there is. It's because of Excel's leap year bug.