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 available) would be to format these correctly as dates in Excel prior to loading into KNIME as then this problem shouldn’t exist.
But, always up for a quick challenge… and assuming that for some reason you cannot change the source data, a method of resolving this would be to convert the excel time serial numbers into Unix timestamps (which are also based on the time that has elapsed since a given date). The “origin” date for Unix is 01 Jan 1970. It’s a shame it’s not the same as the Excel date, but a quick search on google tells me that there are 25569 days difference between the Excel “epoch” date and the unix “epoch” date. Unix timestamps are held as seconds (or milliseconds) since the epoch date, whereas Excel are in 86400ths of a day.
The formula therefore to convert from an Excel to unix timestamp (in seconds) is:
($Invoice Date$ - 25569) * 86400
Since some of your dates are already in a readable timestamp format, we need to split out the different rows for calculation. The Row Splitter can be used to split out those rows containing a “T” in the Invoice Date, and those that don’t.The different rows can then be handled separately, so that those containing a “T” are treated to a simple String to Date&Time conversion, whereas those that don’t are given the full treatment of converting to a Unix Timestamp and then have that converted back into a Date&Time.
Finally the two sets of data can be brought back together and assembled in their original order.
Before
After
I hope the attached workflow is of help:
Convert Excel Dates.knwf (28.9 KB)