Excel "accumulated time" import format

rolandw · July 1, 2025, 10:03pm

Hello,

I am working on a Process Simulation project, and the analysis capabilities of the tool that I have are limited. However, I can export the statistics into an Excel file.

However, when I read that Excel I find the numbers that are in the “hours : minutes : seconds” format for my Dynamic wait time sum and Processing time sum in Excel being read in a Date/time format in Knime.

How can I configure the read node that it gives me the values from the Excel or even in a standardized format like x.y hours (or minutes), so that I can calculate things or display them in a chart … the ultimate goal is to recreate the bar chart from the simulation tool in KNIME so that I have one place to get calculations and visualizations.

Thanks in advance,
Roland

alpayzeybek · July 2, 2025, 1:38pm

Hi Roland,

You can definitely visualize your simulation statistics in KNIME using Bar Chart, even when your time columns are in the format hh:mm:ss (e.g., 5282947:41:02). Here’s my approach:

Use the Excel Reader to import your file.
Use the Cell Splitter node to split the time string using : as the delimiter. This will give you separate columns for hours, minutes, and seconds.
Add a Math Formula node to convert the time to total seconds, using this formula:

$Hour$ * 60 * 60 +$Minute$ * 60 + $Second$

Use the Bar Chart node

This approach gives you a consistent numeric format that works perfectly for aggregation, calculation, and charting inside KNIME — even when working with extremely large durations from simulation outputs.

Below I attached the data and sample flow.

SimulationTimesSample.xlsx (5.8 KB)
Processing time sum.knwf (12.4 KB)

Hope this helps.

Best regards,

Alpay

gonhaddock · July 2, 2025, 2:08pm

Hello @rolandw
Please find a couple of alternatives with ‘Column Expressions(legacy)’ node:

$hours_double$ :

hours = toDouble(regexReplace(column("time_text"), "(.+?):.+", "$1"))
minutes_to_hours = round(regexReplace(column("time_text"), ".+:(.+?):.+", "$1") /60, 4)
seconds_to_hours = round(regexReplace(column("time_text"), ".+:(.+)", "$1") / 3600, 6)

hours + minutes_to_hours + seconds_to_hours

$PeriodTime_text$ :

hours = regexReplace(column("time_text"), "(.+?):.+", "$1")
minutes = regexReplace(column("time_text"), ".+:(.+?):.+", "$1")
seconds = regexReplace(column("time_text"), ".+:(.+)", "$1")

join("PT", hours,"H", minutes, "m", seconds, "s")

BR

rolandw · July 2, 2025, 5:13pm

Thanks guys, I will try out the solutions. I am, curious how the hours calculation will work out that KNIME currently shows in the aggregated date format …

rolandw · July 2, 2025, 8:28pm

The cell splitter works … somewhat … It correctly separated minutes and seconds from the aggregated processing time, but the hours are still displayed in the date format (see screenshot).

When I change the import of these fields to anything besides time (string for example) it still shows me the content in the date format

It is the same when I change the format of the columns in the XLS to “general”
(they are formatted as “time” and that is why KNIME seems to do these things), it won’t change.

Any ideas? I added my simulation result and the relevant parts of my workflow to this note.

Baseline Simulation Current State.xlsx (17.0 KB)
Test Times Excel.knwf (87.5 KB)

rolandw · July 2, 2025, 8:34pm

And just to confirm, it is the same behavior when I import the Processing Time Sum as local date time.

alpayzeybek · July 2, 2025, 10:09pm

Hi Roland,

First of all KNIME exactly does the thing it “should” do, showing the exact value EXCEL stores the data while saving it. Actually excel stores this type of data like this →

3548:36:26 → 1900-05-26T20:36:26.020

if you enter into the corresponding excel cell you shall see the data structure used while saving the data →

therefore, we need to update the resource file while saving in order to store these values as numbers in days.

then rest is easy.

please check the updated excel file and corresponding flow below.

best,

alpay zeybek

Baseline Simulation Current State.xlsx (32.1 KB)

Test Times Excel.knwf (16.5 KB)

rolandw · July 3, 2025, 3:11pm

Thank you. I was sure that KNIME does things correctly, but wanted to avoid changing the source file because I might have multiple simulations that I want to analyze (and that would mean that I need to go into each versus having a workflow defined once that does it for me …).

But hey, maybe that is the way to go …

alpayzeybek · July 3, 2025, 5:53pm

Hi Roland,

I wanted to clarify that this behavior is not due to KNIME itself, but rather a result of the format Excel applies to string values by default.

That said, we could use an anchor timestamp—1900-01-01T00:00—and simply calculate the difference from there. This way, you wouldn’t need to modify the source file in Excel.

Hope this helps! Please find the attached KNIME flow using your original file.

Best regards,

Alpay Zeybek

Baseline Simulation Current State.xlsx (17.0 KB)
Time Diff.knwf (20.0 KB)