Excel Reader and date formatting

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

Hi @izaychik63

This should help you further:

1 Like

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


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.


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

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 :rofl:

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 :wink:

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.

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 :slight_smile:

Hi @bruno29a ,

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

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

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.

1 Like

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