# How to get date of start of the week based on date column

Hi,

I’m pretty new to Knime but I would like to find out how I can calculate the week commencing date based on a column of dates.

For example if my date is 19/6/2020 then the week commencing date (Mon-Sat) should be 15/6/2020.

Thanks!

1 Like

What happens is the date is June 14 Sunday?
Do you want the previous Mon June 8th - Sat June 13 ?
or do you want the Mon-Sat which comes after which is June 15-June 19th?

Hi,

Try with the “Date&Time Extraction” node.
From the date you can extraxt week number and then with “Groupby” node group by week and manual aggregation select minimum of the date column.
Or you can extract day name an then filter mondays (or sundays)

2 Likes

Agree with above - I used the following seq. to get at your problem

1. create a date and time range fixed for 1 day
2. extract date and time fields to get day of week number and day of week name (really just need number)
3. Rule engine
\$Day of week (number)\$ =1 => 1
\$Day of week (number)\$ =2 => 0
\$Day of week (number)\$ =3 => -1
\$Day of week (number)\$ =4 => -2
\$Day of week (number)\$ =5 => -3
\$Day of week (number)\$ =6 => -4
\$Day of week (number)\$ =7 => -5
appending a new column called Start_index
4. date& time shift: select numerical column start_index with Granularity days
5) A second date time shift node with fixed number of days as 5
You always get Monday to Sat no matter what you put in . with Sunday you get the immediate Mon - Sat.

See complete flow here:

5 Likes

Could you upload your workflow here or even better to the KNIME hub?

https://www.knime.com/blog/the-knime-hub-share-and-collaborate

2 Likes

yes 01_Generate_Fixed_Time_Intervalls_x 1.knwf (28.6 KB)

Was playing around with other joins here as I imagined what the solution was - but the flow at the bottom works to solve the problem

mlauber71 let me know what you think?

Hi @webstar,

seems fine to me. And it was marked as Solution so guess it’s works fine for topic creator as well

Br,
Ivan

1 Like

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