merging date and time in KNIME or Excel

#1

Hi everbody

im having some strange trouble with merging two columns. I have one column with a date (dd/mm/yyyy) and one with a time (e.g 19:00) i need these in one column dd/mm/yyyyT19:00 but when i merged them in excel using a merging formular KNIME will read the format as a string, and it doesn’t work with a string to date&time to set it as a date.

If I instead try and keep the 2 columns in excel and wanna merge them i KNIME, the excel reader node reads my time column as a date and time and automatically adds the date 1899-12-31 to all rows even though the time is set as time with you can choose what format it is

does anyone know of this problem or have an idea for a solution

0 Likes

#2

The string to date&time format that is not working is “dd/MM/yyyyTHH:mm” ??

0 Likes

#3

Yes the problem is that I merged the cells in excel using =TEKST(A1;"dd/mm/iiii ") &TEKST(B1;“tt.mm”) and it seems that KNIME are not able to read it as a date at all

0 Likes

#4

The content of the first cell does not appear to match your selected date format.

I would try this as the format “dd/MM/yyyy HH:mm”

1 Like

#5

still the data won’t read with that format06

0 Likes

#6

Sorry, my bad, old eyes cannot spot the difference between . and : :grin:

Change the : in the format to . and try again.

2 Likes

#7

haha good idea but still nothing. it looks right in the output but when i try to e.g. calculate time difference with a Date&TimeDifference in turns up with a minuts count that is way off for 1 day

(Visit date) is the string column that needs to be converted.
after that, i tried to calculate the difference between visit day and booking day… but it seem like it perhaps draws on the “time” column which as earlier mentioned turns time into time and the year 1899. and further more is the column that together with date created “visit date” - made in excel
KNIME_datetime.knar.knwf (9.4 KB)

0 Likes

#8

Hi,

Your workflow does not contain data. Uncheck the reset option when exporting the workflow or upload the data file as well.

Armin

0 Likes