Excel Reader and date formatting

[izaychik63]

Is it possible to add to the data transformation tab conversion to the date/datetime in case
date field in Excel file contains number (not properly formatted as date)?

Thank you

[ArjenEX]

Hi @izaychik63

This should help you further:

How do I convert String to datetime or how do I replace their values with one correct value from same ID

Hi @SoC, and welcome to the KNIME community! The decimal numbers in your InvoiceDate represent the elapsed time since 01 Jan 1900. There are 86400 seconds in a day and so if you multiply the number by 86400 you will get the number of seconds since 01 Jan 1900 So far so good. So we know we can find a way to convert the numbers. However, taking a step back, why do you have a mix of readable dates, and excel internal date values in the source data? I would suggest that the better option (if avail…

[izaychik63]

Thank you, @ArjenEX , for work around. Still, as it is a recurring issue, it would be practical to to have the standard solution.

[takbb]

@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 Serial to DateTime – takbb

[izaychik63]

Sorry for late answer. I think you can optimize your component. Please see picture below

[bruno29a]

Hi @izaychik63 @takbb , somehow I missed this thread.

@izaychik63 , where is column “Birthdate” coming from? It does not look like it belongs to the component. It’s also probably faster to check if(column("Birthdate") < 1000000) rather than converting to string, and then having to calculate the length of that string with if(length(string(column("Birthdate")))<8).

You should also provide the code in text along with your screenshot, so that it’s easier to copy and paste it.

@takbb Not bad as a Component. It does not validate the input data though - but similarly, Knime’s UNIX Timestamp to Date&Time also does not do it

As such, I get these results for these values:

I guess, in theory, they’re still valid datetimes.

But to be fair, it is expecting Excel dates serial numbers, which in theory, would be within “normal” ranges.

Your sample column “Invoice Date” was not cleaned from the component

[izaychik63]

Thank you, @bruno29a, for your interest in my question. I try to find a universal solution when date field could be a date or integer. It looks like Column Expression allows to solve this task in one node. If you see a better way, I’ll be happy to hear from you.

[bruno29a]

Hi @izaychik63 , ah I see. I thought you were proposing something to add to @takbb 's component since you said “you can optimize your component”, so I was wondering where the column “Birthdate” was coming from.

But now I understand what you meant, the Column Expressions that you were suggesting is in your workflow, an alternative to the Component.

Thanks for clarifying

[takbb]

Hi @bruno29a ,

@bruno29a

It does not validate the input data though - but similarly, Knime’s UNIX Timestamp to Date&Time also does not do it

You are right, it doesn’t… It does just one job, exactly as it says on the tin

(besides, when time travel becomes widely available to us KNIMErs, who knows what dates you may need to process… )

@bruno29a

Your sample column “Invoice Date” was not cleaned from the component

Ah yes… I’ve seen that too, and never got round to finding out how to fix it… but strangely I just downloaded it again from the hub and “Invoice Date” isn’t showing for me, so I’m not entirely sure what is going on with that.