Retrieve working days (as in Excel function NETWORKDAYS or NETWORKDAYS.INTL)

[gentile]

Hello fellow KNIMErs,

I have an Excel file from my client that contains carrier data with expected times of arrival.
I need to calculate the working days in minutes from two date/time columns.

Attached is such a file. The last column contains the formula that the client wants me to use:

=IF(A2="","",IF(E2=“FreitagSamstag”,D2,IF(E2=“SamstagMontag”,D2-1440,IF(E2=“SamstagSamstag”,D2,IF(B2="",D2,(C2-B2)*1440-(INT(C2)-INT(B2)-NETWORKDAYS.INTL(B2,C2,1)+1)*1440)))))*1

data-week19_mod.xlsx (3.4 MB)

I tried to re-use (and modify) the workflow that andrejz thankfully provided here: Calculating the number of working days between 2 dates in KNIME - #9 by andrejz but it wouldn’t generate the same results.

Does anyone have an idea how to do that?

Thanks,
g

[andrejz]

Hi,

Are you sure that the Excel formula is OK … If you look for example at the line 65167 in Excel the dates are 8.05.2021 19:12 and 17.05.2021 20:00. The total days between dates is 10 (the first day is Saturday), working days are 6. So if you do not count Saturdays and Sundays the correct result should be 8.400 (5 full days * 1440 minutes + 20 hours*60 minutes) and not 11.568 … or I am missing something?

Regards
Andrej

[gentile]

You are right @andrejz, the formula is not very accurate. In this case the customer has a different calculation if the first day is a Saturday. I think I can figure out those exceptions.

There is one thing I don’t understand with your workflow. For example for this line:

Min. ETA	Max. ETA
2021-04-28T16:58	2021-05-05T14:00

This is Wednesday to Wednesday with one weekend in between.
The relevant part of the formula for this case is the last IF statement:

=IF(B2="",D2,(C2-B2)*1440-(INT(C2)-INT(B2)-NETWORKDAYS.INTL(B2,C2,1)+1)*1440)

and the Excel result is 7022. The result from the workflow is 7074.

Would you have an idea how to deal with this?

[andrejz]

Hi,

I have made this workflow (is not finished yet …filter unnecessary columns and concatenate the two tables) … The idea is to calculate the difference if the date is the same. if the date is not the same exclude the first and last day an calculate the working days between the other days and then at the and add the minutes of the first day and the last day if they are not Saturday or Sunday

Difference in minutes.knwf (2.7 MB)

Hope this helps you

Regards
Andrej

[gentile]

Thanks a lot for your help, @andrejz.
I’ll see if this gets me closer to the Excel results.