Calculation based on date difference conditions

[killYRidols]

Hi all!
This is my first post here. I used Alteryx in the past in a consulting firm and now I’d like to introduce Knime in the company I currently work for, which is heavily excel-based…

In short, I am stuck with using a date_time_difference calculation based on “if” condition.

In detail, I need to calculate the rental days of a rental for a specific calendar month.
Here an example: imagine a car rental or hotel stay that have a start date [SD] and end date [ED] and I need to count the days “impacting” August, ie:
Case A SD 03AUG - ED 10AUG >>> rental days for AUG = 7 (ie date_time_diff 10AUG-03AUG)
Case B: SD 20JUL - ED 10 AUG >>> rental days for AUG = 9 (ie date_time_diff 10AUG-01AUG)
Case C SD 25AUG - ED 10SEP >>> rental days for AUG = 7 (ie date_time_diff 01SEP-25AUG)
Case D SD 20JUL - ED 10SEP >>> rental days for AUG = 31 (ie date_time_diff 01SEP-01AUG)

What I do in Excel:
It is a long but simple formula with many IF functions based on date difference that encompass the above 4 cases (in bold in the formula below) to calculate the rental days for a calendar month
=IF(AND($J2>=CH$3,$J2<CH$4,$AZ2>CH$3,$AZ2<CH$4),$AZ2-$J2,IF(AND($J2<CH$3,$AZ2>=CH$3,$AZ2<CH$4),$AZ2-CH$3,IF(AND($J2>=CH$3,$J2<CH$4,$AZ2>=CH$4),CH$4-$J2,IF(AND($J2<CH$3,$AZ2>=CH$4),CH$4-CH$3,""))))
with:
J2 = start date
AZ2 = end date
CH3 = start of calendar month (ie 01AUG)
CH4 = end of calendar month + 1 (ie 01SEP)

What I would have done in Alteryx
I don’t have it installed it anymore, but if I recall well I would have used the formula tool using the DateTimeDiff function and IF THEN statement to replicate the Excel function above

What can I do in Knime?
Searching the forum I found mainly two cases for similar problems:

• use Date&Time Difference node and then the Rule Engine for conditions: but I get headache if I want to apply to my problem for the large number of columns I should create for each case (and consider that I have to do that for each of the 12 calendar months
• use the Column Expressions node, but I have found example of a syntax that I cannot understand or replicate

So, if the Column Expressions option is the best, can you give me an example of syntax for one of the cases above?
Or, is there any other node o “simple” combination of nodes that I can use?

thanks a lot!
simone

[Thyme]

Hello @killYRidols,

here’s one way to do it. I’m assuming your dates come in 2 separate columns in ISO conform format. That they’re strings doesn’t matter, the Rule Engine will work with Date&Time types as well. What’s important is that the year is there.
You can replace the dates that are “out-of-bounds” with the Rule Engine Node. The borders are feed in via flow variables.
Cast to Date, do dateDiff and you’re done!

dateDiff_conditions.knwf (31.6 KB)

[killYRidols]

Great, thank you for your prompt reply, that’s clever and simple!
I realized I am still trying to reason with an Excel mindset to build formulas

Speaking of which, a last stupid thing I am not able to do.
In case SD and ED are the same, instead of having a 0 (zero) I should get a 1 (or in other words, ED should be shifted by 1 day)
ie. in the sample data SD = 2021-08-10 and ED 2021-08-10 should make 1 instead of 0

With the Excel mindset, it would be natural to me to use a rule engine in such a way
$SD$ = $ED$ => $ED$+1
TRUE => $ED$
…but, as we said, I cannot manipulate a date in here.
…Date Time Shift node alone does not help…
… cannot think how to use the variable “trick” as you did…
help!

btw, I’m very proud of myself that with the Table Row To Variable Loop I was able to apply your workflow to the 12 calendar months in 12 separate columns

thanks!
Simone

[Thyme]

Hi Simone,

the Rule Engine doesn’t do math. Fortunately, the Math Formula Node supports if-conditions: If the result is 0, replace it with 1, else keep it.
With the Rule Engine, one could output a dummy column, use a Row Splitter to separate dates-to-shift and dates-to-skip, apply the Date&Time Shift, concatenate back together, filter dummy column and sort back to previous order. Maybe a bit too convoluted.

When you come from Excel, Loops are the single biggest game-changer! Glad you enjoy working with KNIME.

dateDiff_conditions.knwf (36.1 KB)

[killYRidols]

All clear, thanks again!
Stupid me, I focused on the input (the dates) rather then the output (the number) for applying the if condition.
Also, even if the second option I agree is not elegant, it s a good lesson learnt that sometimes having one or more dummy columns could be an option rather than got stuck trying to do everything in a single node (probably got spoiled by Alteryx’s Formula Tool that allows to a bit of everything)

Cheers!
simone