Hi!!. I have a user who reads an Excel file which he can´t edit. He has a date column (unformatted as such): [image] 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: [image] 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 ) [image] 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 [image] Excel Reader and date formatting KNIME Analytics Platform @izaychik63 , I’ve worked this into a component. Feel free to give it a try and let me know if there are any issues. It might save time if it is something you often encounter. Add this after your excel reader and choose the offending columns. It will append datetime versions of each into your data table.

Excel Date conversion

KNIME Analytics Platform

mlauber71 November 16, 2022, 10:32pm 5

@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?

2 Likes