date time manipulation

Dear all,

I have an excelsheet with three columns (you will find it attached)
1.xlsx (1.1 MB)

  1. Identnr
  2. Installationdate
  3. Latestinstallationdate

Installationdate and Latestinstallationdate are containing 3 types of values:
a - (minus character)
a date
a date time

I am doing some calculations with the sheet and writing into another excel sheet.
Unfortunalety the date colums appear in the outbound file in format yyyy-mm-dd or

I am looking for a smart way to convert as described below:
the - (minus character) should be replaced by nothing
date should be formatted like
date time should be formatted by HH:MM:SS

I tried with string manipulation, string to date, date to string but this seems to me not very smart - at the end of the day I have to do this with 20 sheets and different columns.

I have in mind to do this in one node:
Replace - with nothing for column Installationdate
Replace - with nothing for column Latestinstallationdate
format short date to for Installationdate
format long date time to HH:MM:SS for Installationdate
format short date to for Latestinstallationdate
format long date time to HH:MM:SS for Latestinstallationdate

Folk order of the identnumber should not be changed.

Is this possible?

Thx for help!

BR, Heinz

Hi @Heinz, is the attached file the intended one, as it only appears to contain dates in yyyy-mm-dd format rather than in both date and datetime formats that you described. thanks

Please check cell B40116; there you will find date and time value (please check in edit control in the upper screen)


Dear all,

I think I found a clue: column expressions node…

please see my example workflow.

Copy all attached files to c:\temp
date_time.knwf (21.0 KB)
inbound.xlsx (1.1 MB)
outbound.xlsx (1.6 MB)

inbound.xlsx shows 2 columns:
Customer No and


Installationdate is shown in german notation
Installationdate is date formatted in Excel

With node excel reader I read this file
File Table from this node looks like

installationdate has been changed into Knime format yyyy-mm-ddTHH.MM.SS

With node column expressions I do some manipulations in column installationdate:
When installationdate = - then installationdate= null
when length(installationdate)=8 then installationdate = datetime(installationdate +“T00:00:00”)
when length(installationdate = 19 then installationdate =datetime(installationdate)

with node excel writer I write the file outbound.xlsx
everything is fine, except of the format of the new column:

It is yyyy-mm-dd HH:MM:SS bue it need it in local format HH:MM:SS

How can I realize this?

Thanks for help!

BR, Heinz

If it’s a formatting issue you could try “Write to Excel Template node” then KNIME will use the predefined excel format of the cell.

Hi Daniel,

thx; thanks for quick response; but it does not work - or I am doing something wrong

date_time.knwf (21.3 KB)
inbound.xlsx (1.1 MB)
outbound.xlsx (1.6 MB)

I am reading inbound.xlsx from c:\temp
Outbound.xlsx column c is formated HH:MM:SS

When writing into outbound.xlsx format in column c is to instead of

Do you have a hint?

BR, Heinz

Hi @Heinz
did you "pre"format your outbound sheet as date column?

Hi Daniel,

yes, I did

Looks like your date is a string. You could try convert it into date datatype first and see whether that helps

In Excel the column is recognized correctly as date:

I have seen the write to template behave differently when passing a string data type vs date, with date format set on the excel side.

I have also seen a few quirks overall with date columns not holding all of the template formatting on overwrites. Like losing bold font / center alignment.

Perhaps you could have a few pre-existing string cells in there that can’t match the date format output which are throwing your formatting off. Have you tried checking remove all existing data in the advanced tab of the Write to Template node? Make sure you are writing your headers though.

Hi Knimers,

it’s working partially:
date_time.knwf (21.1 KB)
inbound.xlsx (8.6 KB)
outbound.xlsx (1.2 MB)

I am reading inbound.xlsx from c:\temp
Outbound.xlsx colum b is formatted HH:MM:SS


Please see screenshot from outbound.xlsx:

cell B4 and B5 are formatted wrong (cell B4 marked red = not ok)
contents of focused cell B4 is shown correctly in editbar (marked yellow = ok); this I am expecting in column B

Is this a bug?

Thx for answer!



outbound.xlsx (1.4 MB)

Try overwriting this template file. It looked like you just had the wrong custom format selected. It held for an overwrite on my end.

I uploaded the files again.
Does it work on your side?
Can you pls. send me the workflow?


Didn’t make any workflow adjustments. Just opened it as a temp file and pointed it toward the downloaded excel files, then adjusted the custom formatting on those dates in the output excel file. Ran it again and it held on the overwrite.

Did it not work on your end overwriting the file I uploaded?

Sample Workflow.knwf (2.3 MB)

I placed the inbound and outbound files in the data folder to keep them in the workflow file. I also set it to open the outbound file after writing. (Never tested the opener on a temp file though, so it might require a “save as” first)

Thanks for all the effort from your side!

when executing sample workflow from you I see several messages in the console:
Sample Workflow.knwf (2.3 MB)

Outbund.xls is written to c:\temp; but column B is empty :frowning:

inbound.xlsx (8.6 KB)
outbound.xlsx (394.7 KB)

just want to know, do you mind sharing your operating system’s date and time format? for reference, mine is set to yyyy-mm-dd hh:mm:ss .


It is not writing “outbound” to that temp file location in my workflow. It is writing to the data folder in the workflow. I have set the component to open the data folder after it runs this time so you can access the location more easily. You can copy and paste that Outbound excel file wherever you like and point a Write To Template toward it for overwrite. Be sure to save this workflow before executing it to help you find the data folder.

Sample Workflow.knwf (2.4 MB)

Write to File Template keeps the existing formatting in place of the template file and only writes the data changes. Once I changed the custom formatting in the Outbound excel file to the desired outcome and saved it. Subsequent overwrites worked as expected.

If you want to write to that temp file location with my changes, then you could change the output setting of the Write to template node at the bottom from overwrite to create a new file and point the output to your temp file location. (Leave the template file at the top pointed toward my file in the data folder) Then it should incorporate my format change to the outbound excel file in the workflow data folder.

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