Hello!
When I try to recalculate Excel formulas using the ExcelRadar node on Windows 11 KNIME Ver5.3.0
and import them, I get an error like the one in the image and can’t get the data properly.
The formula and cell contents are as follows. Please refer to the attached Excel for details.
Cell A2: 2024/03
Column B(DAY): =IF(NOW()>DATE(YEAR(INDIRECT(“$A$2”)&“/1”),MONTH( INDIRECT(“$A$2”)&“/1”),DAY( INDIRECT(“$A$2”)&“/1”))+TIME(14,0,0),$A$2&“/1”,“”)
Column C(Value): =IF($B3=“”,“”,ROUND(Sheet1!E3,))
※There is a formula error in the Value column, but it is displayed without any problems in Excel. If you recalculate and collect it in KNIME, an error will occur.
Does anyone know the cause and solution?
Thank you!
test.xlsx (23.2 KB)
KNIME_project_test.knwf (7.3 KB)
Hi @hideki_anbe,
Welcome to the forum.
Thanks for the detailed explanation — I was able to reproduce both issues and here’s what worked for me:
The original formula:
=IF(NOW()>DATE(YEAR(INDIRECT("$A$2")&"/2"), MONTH(INDIRECT("$A$2")&"/2"), DAY(INDIRECT("$A$2")&"/2")) + TIME(14,0,0), $A$2 & "/2", "")
was failing in KNIME when using dates like "2025/3/1".Changing the formula to use a zero-padded day like "2025/3/01" fixed it:
=IF(NOW()>DATE(YEAR(INDIRECT("$A$2")&"/02"), MONTH(INDIRECT("$A$2")&"/02"), DAY(INDIRECT("$A$2")&"/02")) + TIME(14,0,0), $A$2 & "/2", "")
This makes the date string unambiguous and ensures it’s parsed correctly.
For the second issue
Adding how many places at the end worked for me
=IF($B3="";"";ROUND(Sheet1!E3;))
to
=IF($B3="";"";ROUND(Sheet1!E3;0))
Best,
Keerthan
2 Likes
Hi @k10shetty
Thank you for your quick reply!
I fixed it using your advice and it worked without any problems!
The second problem was simply a mistake in the formula…
I’m glad you checked it for me!
2 Likes
