Excel Date conversion

Hi!!. I have a user who reads an Excel file which he can´t edit. He has a date column (unformatted as such):


And he would like to convert it to a date. That is: he receives a “B” type column and wants to convert it to an “A” type column.

We have worked out the following solution:

This is the workflow:
Date conversion.knwf (107.2 KB)

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!! :muscle:) 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.

Sorry for the long post :smiley:

See discussion here


Hi @iperez

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)

gr. Hans


@iperez this strange thing has been noted in the past, but I was not able to find a solution.

But there is this. Apparently a bug in excels code?



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


Hi @HansS Well seen! Thanks!

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.