passing date variables into if statements and operations between them

Hi,
I am trying to write some lines in column expression to check an specific date with another column and then do some mathematical operations which can be easily done in excel. My code is:

if ((column(“Start date”)>“2024-01-31”) || column(“Invoice date”)>“2024-01-31” || column(“End date”)<“2024-01-01”)
{0}
else if ((column(“Invoice date”)>=“2024-01-01”) && (column(“Invoice date”)<=“2024-01-31”))
{column(“Post-discount contract value”)*min((“2024-01-31”-column(“Start date”)+1),(column(“End date”)-column(“Start date”)+1))/(column(“End date”)-column(“Start date”)+1)}

else if ((column(“End date”)>=“2024-01-01”)&&(column(“End date”)<=“2024-01-31”))
{(column(“End date”)-“2024-01-01”+1)*column(“Post-discount contract value”)/(column(“End date”)-column(“Start date”)+1)}

else
{“”}

the output data type is double. “Start date”, “Invoice date” and “End date” are local dates and I don’t know how I can use some functions like Min() on dates and I guess knime can’t understand that “2024-01-31” is a date and not an string.

@Homa

Welcome to KNIME Community Forum.
You are right: KNIME doesn’t understand “2024-01-31” is a date if it sees that as string. But there is ‘String to Date&Time’ node available to convert string to date (as per your example) type. There is also ‘Data&Time to String’ node to make reverse convertion.
Thus, please try to convert “Start date”, “Invoce date” and “End date” to date format first.

Happy KNIMEing,
Kaz

1 Like

Thanks @Kazimierz for the prompt answer.

I’m afraid my columns “Start date”, “Invoice date” and “End date” are already date time. What I want to to is to subtract a specific date from the whole elements of these columns for example calculating the days between 31.Jan.24 and all of the elemnts existing in column “Start date”:

“2024-01-31”-column(“Start date”)

In fact what I need to calculate is this:

min((“2024-01-31”-column(“Start date”)+1),(column(“End date”)-column(“Start date”)+1))
any suggestion?

I’m not sure how your data look like, so I’m just guessing: instead of “2024-01-31” string I would use date(2024-01-31) function in Column Expression.
Please find attached workflow for your inspiration
Days_between_dates.knwf (85.8 KB)
The 3rd branch finds min date for sample dates.

I hope this helps a bit

1 Like