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?
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?
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.
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.
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
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
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.