The Excelize XNPV only works when the dates are strings, whereas the Excel XNPV only works when the dates are dates

[grahamorrell]

Description

Steps to reproduce the issue:

1. Set up an XLSX file as pictured below - this is using the same test data as your code based XNPV tests
2. Specifically column A is formatted as dates in US format (mm/dd/yyyy)
3. Specifically column E is formatted as Excel dates (UK format in my case)

Describe the results you received:

In Excel - the XNPV formula in C1 does next expect the dates as text and does not work. You must supply the dates as dates (as seen in cell G1)

In Excelize the opposite is true. Calling CalcCellValue on C1 correctly returns 4447.938 like Excel. Calling CalcCellValue on G1 returns #NUM!

Describe the results you expected:

Excelize behaviour to match Excel

Output of go version:

go version go1.23.1 windows/amd64

Excelize version or commit ID:

github.com/xuri/excelize/v2 v2.8.1

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

[xuri]

Thanks for your issue. Could you provide the workbook attachment without confidential info?

[grahamorrell]

Sure, please find attached here. Note I don't think it is a US vs UK date format issue. I think it is a string vs date format issue.

XNPV_Issue.xlsx

[xuri]

Thanks for your feedback. I fixed this, please upgrade to the master branch code by go get -u github.com/xuri/excelize/v2@master, and this patch will be released in the next version.

[grahamorrell]

I can confirm that the latest master is now working as expected - thanks so much for the quick turnaround!