Excel data, date column in excel when read in knime was reading in 2 different formats

[chaithuj]

Input file used : Test_Data.xlsx (377.8 KB)

Used Excel reader to read this file and the output isas below

what could eb causing this issue and how can i fix it

[rfeigel]

There’s something wrong with the data formatting in your Excel file. I’m not sure what’s causing the problem, but you should investigate what’s going on back at Excel. Here’s what the data looks like when converted to text.

[mlauber71]

@chaithuj you could try something like this

Different date format in one Column

Hi @angelmilo , I put something together based on the assumption that data with slash, is M/d/yyyy, meaning I have to swap the position of M and d when converting them to dd-MM-yyyy. This is what the workflow looks like: [image] Results after the manipulation: [image] You can see the original input data in the left column, and you can see for the dates with slash, they are properly formatted, including their time. Results after converting the manipulated data to type datetime: [image] …

[takbb]

Another approach is just to chain String to Date&Time nodes, making sure you have unticked “Fail on error” and then use Column Merger to merge the results:

[takbb]

But the “simplest” solution is to do it all in a single String to Date&Time using “optional” date masks:

[yyyy-MM-dd][dd/MM/yyyy]

This will convert both dates within the same column

[rfeigel]

@takbb I clicked “Like” for your last solution, but I’ve got to comment. That’s a stroke of genius. I would have never figured that out. Kudos to you.

[takbb]

Thanks @rfeigel ! Too kind

I should add a cautionary note. It is possible to get “carried away” with the date/time masks though, as I once did, as it can be tempting to see what is possible with lots of optional values to create some kind of “universal data mask”!. This would make the mask very long, and if you do that, you may find that you inadvertently make the node difficult to configure, in future as it becomes over-wide… as described in this post (along with the fix should you ever find yourself in that situation)

Oversized Config dialog on Date&Time to String and String to Date&Time nodes

Hi all, I recently (last day or so) updated my KNIME from 5.2.3 to 5.2.4, although this may be unrelated. This morning, I tried using Date&Time to String nodes and the configuration screen is oversized. It’s never been a problem before, but it seems to be trying to use up probably twice the width of my available screen, and doesn’t resize “nicely” either. This is on Windows 10 This is also happening to String to Date&Time, but interestingly I haven’t seen it on any other nodes. Here is a gif…

[rfeigel]

Thanks for the heads up. There’s never a free lunch.

[chaithuj]

@takbb, Thanks a lot for your time helping me
Perfect, it was able to handle both date formats and convert to yyyy-mm-dd
Is there a way we can get the output as MM/dd/yyyy

[chaithuj]

Thank you @mlauber71

[chaithuj]

@takbb, I was able to add one more Date&Time to String to get the format, i wanted

[takbb]

Hi @chaithuj , once you have the date being held as an actual Date datatype, technically it has no format. A date only has a format when it is rendered but unfortunately I don’t think there is an option in KNIME to change the format in which Date datatypes are rendered.

To display in a different format, you need to convert it back to a String and describe the format that you want:

e.g. using the Date&Time to String node, and specifying a format of MM/dd/yyyy

EDIT: I see you found the same answer while I was writing this!

[chaithuj]

Thanks @takbb
Your solution is brilliant and simplest.
Thanks a lot for all the help