Noob Help: Convert date value to dd/mm/yyy

[plunketj]

Hi Team,

Very noob question I’m sure but I can’t find the answer to this one.

I have a table coming in with a start moment column. The data is formatted as ‘43577’. It was originally a DOUBLE but I’ve converted it to an INT which in Tableau would allow me correct conversion to a dd/mm/yyyy format.

I can’t for the life of me figure it out in KNIME though. How do i convert from 43577 to ‘15/04/2019’?

Thanks,

Josh

[quaeler]

Maybe this is a Tableau-specific derivation - but nothing pops out at me as to how 43577 could turn in to that date. Is it hours since some epoch? Probably not days since that’s about 120 years, but maybe the epoch was 120 years ago.
Generally, time is number-stored as the number of milliseconds since standard epoch (new year’s day in 1970)… so such a small number representing a date seems odd.

[plunketj]

If you put that number into Excel and change the formatting it changes to a dd/mm/yyyy format. I see this type of format all the time in Excel.

[quaeler]

Great - there’s the magic secret… according to this the epoch is new year’s day 1900, and each number is a day since.

You could use the date & time shift node - but that seems like a pain in the ass. Here’s a workflow. Perhaps you could export the dates in a different format (like an YYYYMMDD string or milliseconds since epoch or …) for which there are nodes that do this conversion specifically.
plunket excel number to date.knwf (6.8 KB)

[plunketj]

Thank you so much!

Understanding how the calculation was meant to work set me on the right path.

I solved it with the Date/Time Shift (legacy) object.

I found the Data&Time Shift node won’t accept my input column as it’s an INT while this node appears to want something that is already in the date format. I’m probably missing something obvious. But it’s working which is the important thing.

[SimonS]

Hi @plunketj,

Nice to hear that it’s now working for you. However, it’s usually recommended to use the non-legacy nodes.
You will have to set the INT column as shift column, see here (the INT column is called column1):

As date column you will have to set a local date column that has the date 01/01/1990. See the workflow of @quaeler. That’s the date you will shift by the number of days that is contained in the INT column.

Hope this clarifies it,
Simon