Format Date in Write To Excel Template

[albpesca_knime]

i’m experiencing an issue when writing a field of type “date” into an excel
file.
Actually i write the field of type date with the node “Write To Excel
Template”.
Into the template file the column is formatted as a date in italian format
dd/MM/yyyy.
Knime writes the date but the excel file shows it in the format yyyy-mm-dd
but excel doesn’t recognized it as a real date…
and only by clicking double into a cell,it shows the date in the desired
format.
Do you know why? Is there a correct way to write a date in italian format and
having excel that recognize it correctly?
Thank you all!

[aworker]

Hi @albpesca_knime & welcome to the forum

The following recent topic addresses exactly the problem you described with various proposed solutions:

Change the date format of the output report

Hi, I have been googling for hours now to find a right solution on my knime problem. How will I change the date format of the output report from yyyy-MM-dd to dd-MM-yy. I tried date&time to string node to get the format that I prefer. But when I use string to date&time node, the date format goes back to the original format. Need help.

Hope it helps.

Best
Ael

[takbb]

Hi @albpesca_knime , and welcome!

Further to the information in the thread mentioned by @aworker , one of the suggestions leads to the use of the “Continental Nodes” for XLS Formatting, (as noted there by @iCFO) . These nodes are very powerful and can achieve what you require, but there can be a bit of a learning curve initially as you need to know how to create “tags” for the cells, or cell ranges that you wish to format.

The trouble I had was that I needed to do this very occasionally and then I could never remember how to do what I needed to do!

For this reason, I created a component that works with the Continental Nodes, and does all the hard work of generating tags, to cover most scenarios, so you don’t really have to think too hard about it.

XLS Control Table Auto Tag Generator – takbb

Add it to workflow like this:

The second data output port supplies tags to cover most scenarios from the input data. For date columns, it will generate a tag named “t:Local Date”. The created tags can then be referenced using the Continental XLS Cell Formatter node, by connecting this port, along with the green “XLS Formatter” port. The upper port is your data which must be passed to an Excel Writer, and then the created XLSX file (without formatting) is used by the XLS Formatter node to generate a formatted XLSX file.

In the attached demo workflow, you can see that the date is formatted as required:

Formatting Excel Dates.knwf (113.7 KB)

You may still find you need to understand a little bit about how this works, so a more detailed post about using the Auto Tag Generator component is available here:

Auto generating common tags for Continental XLS Formatting Nodes

I hadn’t really got into using the Continental XLS Formatting nodes for my own work until about a week ago. They are a great extension to KNIME, but I just had not really had a need to format my output spreadsheets. I’d tried to assist on the odd forum thread but never felt I could just go format a spreadsheet “on demand” without having to go back and re-read the tutorials or examples to remind myself how it worked. Even when I might have wanted to, I put it in the “too-hard” basket and decided…

[iCFO]

Very cool component @takbb !

Thanks for that share.

[AnotherFraudUser]

@albpesca_knime beside the already answered workaround - could you provide an example workflow for this? Will check to fix it in the node as well

[Daniel_Weikert]

By the way is it already compatible with version5?
br

[AnotherFraudUser]

hopefully this weekend
had a bit of a hold up due to vacation
Hope will not be the last one to provide it to Steffen

[albpesca_knime]

Dear @takbb , thank you very much for the great solution! It worked very well!!!

[takbb]

You’re welcome @albpesca_knime , I’m glad it worked for you