Introduction
Hi, I am Akira, the editor-in-chief of Data Without Code. In our last tutorial, we learned how to sort data and automatically extract the Top N records for your business reports. Your data is now clean, sorted, and perfectly organized.
But as a DX manager working in Japan with international teams, I know there is one specific type of data that strikes fear into the hearts of all Excel users: Dates.
Have you ever opened an Excel file and seen a date turn into a random number like 45210? Or perhaps your US colleagues sent data as MM/DD/YYYY, but your European system reads it as DD/MM/YYYY, completely ruining your monthly sales summary.
In this tutorial, I will show you how to end this nightmare. We are going to master date and time manipulation in KNIME to effortlessly standardize formats and extract months and years—all without writing a single complex formula.
Step 1: Convert Text to Real Dates
When you import data from a CSV or a CRM, dates often enter KNIME as regular Text (Strings), not as actual Date objects. Before we can extract a specific month, we must tell KNIME: “Hey, this column is a date!”
We do this using the String to Date&Time node.
- Drag the String to Date&Time node onto your canvas and connect it to your dataset.
- Double-click to open it. In the top box, move your date column (e.g., “Order Date”) to the right side (Include).
- Now, look at the “Date format” box. KNIME is incredibly smart—if you click the “Guess data type and format” button, KNIME will automatically scan your text and figure out if it is US format, European format, or an ISO standard format (like
yyyy-MM-dd). - Click OK and execute. Your column header will change from an “S” (String) to a calendar icon. Your data is now safely locked in as a true Date!
Step 2: Extracting Months, Years, and Quarters
Now that KNIME recognizes your column as a real date, analyzing it becomes incredibly easy.
In Excel, you would normally have to create three new columns and type =YEAR(A2), =MONTH(A2), and maybe a complex VLOOKUP to figure out the business quarter. In KNIME, we handle all of this in one click using the Extract Date&Time Fields node.
How to Use the Extract Date&Time Fields Node
- Search for the Extract Date&Time Fields node and connect it to your workflow.
- Double-click to configure it. At the top, select your newly converted “Order Date” column.
- Below that, you will see a list of checkboxes. Simply check the boxes for the exact data you want to extract:
- Year
- Month (number) or Month (name)
- Quarter
- Week of year
- Day of week (name) (Perfect for analyzing if sales are higher on weekends!)
Click OK and press F8 to execute. When you view the output table, KNIME has automatically generated perfect, clean columns for every checkbox you selected. No formulas required!
Why This is a Game Changer for Reporting
By extracting the “Year” and “Month” into their own columns, you have just unlocked the full potential of automated reporting.
Remember when we learned how to recreate Pivot Tables using the GroupBy and Pivoting nodes? You can now route this newly extracted “Month” column directly into a Pivoting node to instantly generate a Month-over-Month sales matrix.
Because the “String to Date&Time” node locks in the format, it does not matter if someone sends you a messy date next week. KNIME will parse it, extract the month, and update your pivot table automatically.
Conclusion: Your Next Steps
Congratulations! You have officially conquered the most frustrating data type in the business world. Your pipelines can now handle time zones, international formats, and date extractions flawlessly.
We are almost finished with our Data Prep & ETL module. You have learned how to clean strings, handle missing values, and extract dates. But what if you need to create custom business rules? For example: “IF the customer is in Japan AND sales are over $1,000, THEN label them as VIP.”
If you are tired of writing endless nested IF statements in Excel, join me in our next and final Data Prep tutorial: Rule Engine node tutorial: Writing IF-THEN logic without code!
