# Calculating the number of working days between 2 dates in KNIME

I am new to KNIME and I am having to calculate the number of working days between two dates - I have looked at other discussions but for some reason, they are not working for me. Maybe I am missing something/ doing something wrong?

For context: My data source has dates ranging from Jan 2019- May 2020.

Can you clarify what is not working? Could you show an example of what you tried?

â€“ P.

Hi,

One possible solution will be to use â€śDate&Time to Stringâ€ť node. In the Date Format field enter â€śeeeâ€ť, this will transform the date to day name. Continue with â€śRule engineâ€ť node and set a new column with values weekend and workdays something like this:
(\$Date\$ = â€śSatâ€ť) OR (\$Date\$ = â€śSunâ€ť) => â€śweekendâ€ť
TRUE => â€śworkdayâ€ť

A second â€śRule engineâ€ť is needed to set the range of days for example
(\$Date\$>â€ś2020-05-05T00:00â€ť) AND (\$Date\$<â€ś2020-05-15T00:00â€ť) => â€ścountâ€ť
TRUE => â€śnot countâ€ť

And then use â€śGroup byâ€ť to count workdays

Kind regards
Andrej

p.s. Maybe you need another table with holidays to tag and not count them

1 Like

Once you have converted to the KNIME Date&Time format, this sounds like a job for the Date&Time Difference node.

@ipazin has a workflow here that demonstrates how this node works inside a loop:

If you need a simpler example, maybe I can quickly make one.

EDIT: I missed the condition that this is about working days. The post below has some good examples that may help.

4 Likes

Hi,

Try this
Count workdays.knwf (45.2 KB)

Double click to â€śChoose date rangeâ€ť component to select start and end date.

p.s. Or this for holidays

Count workdays, holidays,â€¦knwf (54.1 KB)

3 Likes

Hi there @victoriajmay,

welcome to KNIME Community!

Seems this is frequent request and from experience solution depends on data format so sharing sample might help

Br,
Ivan

Hi @ipazin and KNIME developers

The calculations with dates are frequent and KNIME offers some nodes to do this but I do not know for the node that made calculations like â€śworkdayâ€ť and â€śnetworkdaysâ€ť excel functions.
If this node doesnâ€™t exist â€¦ it can be done or upgrade the â€śDate&time Diferenceâ€ť node to do this job?

Thank you
Andrej

1 Like

Hi @victoriajmay and others

Interesting topic. I created a workflow calculate_working_days.knwf (82.9 KB) that takes a table with two columns (startdate and enddate) as an input and returns the number of workingdays (Monday to Friday).

gr. Hans

7 Likes

Hi,

I have made some research and found the math formula for calculate the working days between two dates
( https://alecpojidaev.wordpress.com/2009/10/29/work-days-calculation-with-c/ )

and here is the workflow

NumberWorkDays.knwf (16.5 KB)

5 Likes

@andrejz, nice
gr. Hans

Hello!

Nice one @andrejz

Additionally now there is feature request to handle this easier. (Internal reference: AP-14411)

Br,
Ivan

4 Likes

Are you asking strictly about â€śall days that are not weekend daysâ€ť or are you also needing to avoid counting holidays?

what about creating a date table like the sort of date dimensions used in data warehouses, where not even weekends, but also national holidays, company holidays, â€¦ are marked. If interested, I can provide a SQL-Script to create and fill such table. If once created, the table can be stored in database or even in excel and used again and again. Less than 3.700 rows for 10 years is not that much.

Best regards,
Karl

2 Likes

Thanks so much for this!

1 Like

Hi ipazin
how can i create week number of date?

Hi,

Look at Extract Date&Time fields node. There you choose which data from date and time you want to extract, one of them is week number

2 Likes

thank you

1 Like

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.