Add holidays days in addition to the date&time shift

Hey guys,

In my flow I need to shift my start date with a fixed value but my main issue here is that if there is a weekend or public holiday, the target date must shift considering also the added days.

For example if the target date is on Wednesday is ok but if is on Sunday I need to add two days (considering Saturday and Sundays) in order to have the new target date on Tuesday.

Many thanks

This question comes up fairly often. Have you checked all the previous posts?

2 Likes

Hi,

my issue here is not related only with date and target date but how the target date&time is moving considering also the time element

Date Additional duration
2021-01-05T08:33:04 0.25
2021-01-05T08:34:21 0.25
2021-01-05T10:05:53 0.0375
2021-01-05T10:31:08 3.0825
2021-01-05T10:40:11 0.375
2021-01-05T10:40:15 0.25
2021-01-05T10:53:50 0.1625
2021-01-05T10:54:17 2.875
2021-01-05T11:13:22 0.85
2021-01-05T11:13:38 0.25
2021-01-05T11:19:42 0.4875
2021-01-05T11:23:19 2.34625
2021-01-05T11:32:15 0.3375
2021-01-05T11:32:26 0.9125
2021-01-05T11:54:11 0.7875
2021-01-05T12:04:50 0.3
2021-01-05T12:19:07 0.0375
2021-01-05T12:25:32 4.6525
2021-01-05T12:32:36 0.025
2021-01-05T12:40:26 0.8
2021-01-05T14:34:02 0.24375
2021-01-05T14:58:28 1.9225
2021-01-05T15:40:04 2.94125
2021-01-05T15:55:42 0.15
2021-01-05T16:01:45 0.00125
2021-01-05T16:03:31 0.0
2021-01-05T16:12:27 0.1
2021-01-05T16:18:22 0.2625
2021-01-05T16:19:04 0.25875
2021-01-05T16:25:02 0.275
2021-01-05T16:25:33 0.21
2021-01-05T16:30:11 0.1
2021-01-05T17:02:02 0.3125
2021-01-05T17:26:09 2.68375
2021-01-05T18:23:08 0.0
2021-01-05T18:25:49 0.2375

Using this table, I want to see how the date is moving considering the 6th & 7th of january as holiday.

R

Would you please explain what it is you’re trying to accomplish, in detail?

  • What are the starting dates?
  • What is the fixed value you’d like to add?
  • You talk about start dates and target dates, but the table you just posted only has “Date”. Is this a start date? A target date? something else?
  • What is the meaning of the “Additional duration” column in the table in your last post? As far as I can tell, that’s not a KNIME duration format. What are the units?

my issue here is not related only with date and target date but how the target date&time is moving considering also the time element

It’s unclear to me what this means. You haven’t actually said what the issue is or how times are supposed to be considered. Your post first talks about shifting dates, but then your second post talks about including times. This is confusing.

Using this table, I want to see how the date is moving considering the 6th & 7th of january as holiday.

I’m not sure what this means. All the dates in the table are January 5, 2021 (or June 1, 2021). What exactly are you looking for?

It would be great if you could provide some explanatory example data as well as examples of the output you’re aiming for.

2 Likes

Hi @Reader91 , on top of the questions that @elsamuel has asked, how do you identify a holiday? Is there a table that would say which dates are holidays?

Holidays are usually not international. For example, even Thanks Giving in North America is not on the same day in the US and in Canada.

But in general, I agree with @elsamuel that the request/issue is not clear. There are some undefined business rules.

Along with the clarifications, you can also add some sample input data, and what the expected output would be so we can verify if we indeed understood what’s expected.

2 Likes

Hi guys,

first thank you for your response.
Below you will find a small tab with explanation because I realized I’ve been a bit imprecise in my description

  • What are the starting dates?

The date you will find is the start date&time that I would like to move

  • What is the fixed value you’d like to add?

I’ve forgot to say that I don’t have anymore a fixed value to be added to the start date but I would like to use the column that not correctly I’ve called Additional duration (now called Additional working days)

This column represent the additional working days&hours (formatted as double number) to be added to the start date&time.

It is important to highlight that the working time within the days is 9 to 17 (8 hrs)

My idea is to build a flow that is similar to this one

But instead of consider the shift based only on the working date I would like to put inside also the variable related to the working time

ID Start date Additional working days Target date
54 2021-01-05T16:25 0.5 2021-01-07T12:25
157 2021-01-12T09:20 5.0 2021-01-19T09:20
169 2021-01-11T12:30 3.5 2021-01-14T16:30

For example the first row (ID 54) is shifting an additional date because adding 0.5 working days (4 working hrs) to the start date the potential target date would be on 2021-01-06 at 12:25 but considering that day is public holiday, is shifting to the 7th of january.
The second row ( ID 157) is shifting two additional days (on top of the 5 additional working days->40 working hrs) because we have saturday and sunday.
The third row is ok because the 14th of january is thursday and the target time is between the working hours (3.5 working days->28 working hours)

Below the list of the holidays

ID

2021-01-01
2021-01-06
2021-04-04
2021-04-05
2021-04-25
2021-05-01
2021-06-02
2021-08-15
2021-11-01
2021-12-08
2021-12-25
2021-12-26
2021-12-31

Many thanks
R

Hi guys

this is the DB I’m using currently

DB for date&time shift.xlsx (237.3 KB)

R

Hello @Reader91,

have you had any success with your task? This indeed seems a bit tricky as you have both working hours and non working days. Also I have seen you have entries before 9 and after 17 so it’s not clear how to approach to these entries. Is 18h + 1h => 10h or 11h?

Anyways one approach could be to separate column Additional working days into column with only days where you can then first use approach from above linked workflow, and column with hours, minutes and seconds (duration not larger than 8h) which you would then add to already shifted value to move it to next non working day. Does it make sense?

Br,
Ivan

1 Like

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