The idea is to use the Excel date management explicitly: the number in the “B” column is the number of days form 1900-01-01 (Date systems in Excel - Microsoft Support)
Adding the number in the “B” column to the base date should give you the correct date but we had to tweak it so that the starting date is (1899-12-30) otherwise it adds 2 days to the correct date.
Two things:
1.- Even though we have managed to get what we want (Everything is possible with KNIME native nodes…almost!! ) I’m wondering if there is a simpler way to do it. We don’t want to use Python or Java since the people who is going to work with the solution are not programmers and would prefer a KNIME-comprehensible solution.
2.- We have pinpointed a problem in row 814: 1900-01-20 gets converted to 1900-01-19. We haven´t been able to figure out why.
This is really strange. I created a time range starting at 1899-31-21, and used a Counter node to count the days since. And indeed I see differences . Maybe MS sees 1900 as a leap-year, but it isn’t. But than there is still a difference of 1 to explain.
see Date conversion_v2.knwf (426.2 KB)
In addition to the above. When you create a date-range in Excel starting from 1-1-1900, it creates 29-2-1900. But that is not correct. 1900 is not a leap-year.
So when you use a numbered value from a date generated in Excel, you have always a difference of 1 (from 1-march-1900). If you read in a small Excelfile sample, you see what happend. KNIME knows 1900 is not a leap year, but converts 29-2-1900 to 1-3-1900. And you end up with 2x 1-3-1900 in your KNIME file.
gr. Hans
) I’m wondering if there is a simpler way to do it. We don’t want to use Python or Java since the people who is going to work with the solution are not programmers and would prefer a KNIME-comprehensible solution.
