Reclassifying dates based on a business rule

[RWars]

I am trying to build a workflow around a certain business rule involving 2 dates. I've attached the workflow that I've built so far. Here is the rule: [Date (Week Beginning)] must be adjusted so that it is 7 or more days before the [Date submitted] and [Date (Week Beginning)] must also be changed to a Monday if it is not (I used the DateTimeAdd([Date (Week Beginning)], -x, "days") formula to adjust these).

I've built the majority of the workflow, but there is one case that I am having trouble figuring out. This case involves those [Date (Week Beginning)] cases that are not Mondays and are less than 7 days before their associated [Date submitted].

For example, we can see in the 'Days between Date and Submission Date' column that these are all < 7 days. What I'm looking to do is add a new column containing the date which would be the Monday before the submission date that is at least 7 or more days before the 'Date (Week Beginning)'. So for Transaction 660 this date would be 2017-11-20.

Hope that makes sense, I've attached the workflow that I created so far.

[AngelosPachis · Accepted solution]

Hi @RWars ,

Sorry I couldn't access your input file because you have sent over a standard workflow and the data is not included within that. However, I have replicated the data based on the image you provided in the attached image.

I have created a workflow that will estimate the first Monday that is at least 7 days before the Date submitted. It has worked for the mocked data, and I think it will work for your task as a whole.

What the workflow does is to look at the day of the week of each "Date Submitted" and based on that will find the days you have to subtract to be taken back to the previous Monday which is at least 7 days away. 

That is achieved with a switch function. So if  the Date Submitted is a Sunday, you should move back 6 days to go to Monday, but because that is not more than a week away, you should also add 7 extra days, so the total days you have to go back is 13.

Hope that helps, let me know if you have any questions on the workflow.

Cheers,

Angelos

[RWars]

Thanks Angelos, this solution does exactly what I was looking for!