File.CalcCellValue fails for "=DATE(2020,10,21)+1" formula

[alexbrainman-anz]

Description

I am using

=DATE(2020,10,21)+1 and =MIN(DATE(2020,10,21),DATE(2020,10,21),DATE(2020,10,22))

formulas in my Excel files.

I am calling File.CalcCellValue method on those formulas, but it returns error and no correct value.

Steps to reproduce the issue:

I used this commit 9d4c2e6 .

I made these changes to the code:

diff --git a/calc_test.go b/calc_test.go
index 123db37..9bfd937 100644
--- a/calc_test.go
+++ b/calc_test.go
@@ -1540,8 +1540,10 @@ func TestCalcCellValue(t *testing.T) {
                "=XOR(1>0,0>1,INT(0),INT(1),A1:A4,2)": "FALSE",
                // Date and Time Functions
                // DATE
-               "=DATE(2020,10,21)": "2020-10-21 00:00:00 +0000 UTC",
-               "=DATE(1900,1,1)":   "1899-12-31 00:00:00 +0000 UTC",
+               "=DATE(2020,10,21)":   "2020-10-21 00:00:00 +0000 UTC",
+               "=DATE(1900,1,1)":     "1899-12-31 00:00:00 +0000 UTC",
+               "=DATE(2020,10,21)+1": "2020-10-22 00:00:00 +0000 UTC",
+               "=MIN(DATE(2020,10,21),DATE(2020,10,21),DATE(2020,10,22))": "2020-10-21 00:00:00 +0000 UTC",
                // DATEDIF
                "=DATEDIF(43101,43101,\"D\")":  "0",
                "=DATEDIF(43101,43891,\"d\")":  "790",

I run this command:

go test -run=TestCalcCellValue

Describe the results you received:

I see this output:

--- FAIL: TestCalcCellValue (11.61s)
    calc_test.go:2256:
                Error Trace:    /Users/brainmaa/src/github.com/qax-os/excelize/calc_test.go:2256
                Error:          Not equal:
                                expected: "2020-10-21 00:00:00 +0000 UTC"
                                actual  : "0"

                                Diff:
                                --- Expected
                                +++ Actual
                                @@ -1 +1 @@
                                -2020-10-21 00:00:00 +0000 UTC
                                +0
                Test:           TestCalcCellValue
                Messages:       =MIN(DATE(2020,10,21),DATE(2020,10,21),DATE(2020,10,22))
    calc_test.go:2255:
                Error Trace:    /Users/brainmaa/src/github.com/qax-os/excelize/calc_test.go:2255
                Error:          Received unexpected error:
                                strconv.ParseFloat: parsing "2020-10-21 00:00:00 +0000 UTC": invalid syntax
                Test:           TestCalcCellValue
                Messages:       =DATE(2020,10,21)+1
    calc_test.go:2256:
                Error Trace:    /Users/brainmaa/src/github.com/qax-os/excelize/calc_test.go:2256
                Error:          Not equal:
                                expected: "2020-10-22 00:00:00 +0000 UTC"
                                actual  : ""

                                Diff:
                                --- Expected
                                +++ Actual
                                @@ -1 +1 @@
                                -2020-10-22 00:00:00 +0000 UTC
                                +
                Test:           TestCalcCellValue
                Messages:       =DATE(2020,10,21)+1
FAIL
exit status 1
FAIL    github.com/xuri/excelize/v2     12.129s

Describe the results you expected:

PASS
ok      github.com/xuri/excelize/v2     12.396s

Output of go version:

go version go1.21.6 darwin/arm64

Excelize version or commit ID:

9d4c2e6

Environment details (OS, Microsoft Excel™ version, physical, etc.):

I use MacOS. I do not have Excel installed.

Thank you very much for your assistance.

Alex

[xuri]

Thanks for your issue. This issue has been fixed, please upgrade to the master branch by go get -u github.com/xuri/excelize/v2@master, and this patch will be released in the next version. Note that, there some changes in the behavior of the calculation result of the DATE formula function, when you set the cell formula with the DATE formula using Excel, Excel will be set the cell value as a number, and set the cell with a date number format (if the cell does not have any existing formats). When you set the cell formula with the DATE formula using the Excelize library, the SetCellFormula function doesn't change the cell format, so you also need to create a style with number format with the NewStyle function, you will get a style ID, and then you need to set the formula cell by the SetCellStyle function with that style ID, otherwise, you will get numerical calculation result when calculating cell value by the CalcCellValue function.

[alexbrainman-anz]

... This issue has been fixed, please upgrade to the master branch by go get -u github.com/xuri/excelize/v2@master, and this patch will be released in the next version.

I tried your fix. It works for me. Thank you very much for quick resolution.

... Note that, there some changes in the behavior of the calculation result of the DATE formula function, when you set the cell formula with the DATE formula using Excel, Excel will be set the cell value as a number, and set the cell with a date number format (if the cell does not have any existing formats).

The behaviour change is fine with me. I compare dates. So comparing two numbers works for me.

Thanks again.

Alex