Generate rows - backwards dates without weekends

[nsmith293]

Hello,

I have a scenario where i need to generate rows starting from a date field and working backwards. 

For example:

Completion Date = Fri March 16th

I need to create 3 rows to represent 3 days of estimated work that will hit that Completion Date.  In this case- not a problem to increment backwards until the 3 days are reached.

However, weekends are giving me an issue. I cannot count them in my logic.

Ex.

Completion Date = Tuesday March 20th

I need to back off 5 days (or 10, covering 2 weekends....).  That will end up only going back to Thur March 15th because it counted Sat/Sun. It should have created rows back to Tue March 13th excluding Sat/Sun.

The Generate Rows function doesn't seem to allow me to do this.  Ideally, I'd love to say "generate xx number of rows where the date doesn't land on Sat or Sun".  But I don't see how to do this and still have a date value generated per row.

Any ideas?

Thanks!

[MarqueeCrew · Accepted solution]

Try building logic for number of potential days as:

Divide by seven and use the ceil() function to round up. Take that result and multiply it by two. That's the number of max weekend days to add.

So now you have too many days. So filter out days that are in Saturday or Sunday by use of a date time format looking for the day of the week (%A) in an in() function.

You can use a multirow formula to count the number of desired days. Filter out any extras.

From the airport, without my laptop, this would be my back of the napkin approach to start your exercise with.

Cheers,

Mark

[nsmith293]

Mark,

Thanks for your quick reply. I can see how you approached that and it makes sense.  However, if I'm reading it correctly, that would only work if the numbers of days might actually overlap a weekend.  For example, I could not use that as a generic approach for a scenario where the Completion Date is a Friday and I want to back off 3 days.  No weekend impact in this case.  It sounds like I'd need to build in first-layer of logic that says if the day is X and the days to subtract are Y AND that could touch/cross a weekend then use the approach you noted.

One thing I did finally realize is that i can control the loop iterations to skip weekends using IF THEN ELSEIF.  Unfortunately, I still don't have a way to say "here is the date you must to go to", so that loop logic doesn't help much at this point.

Thanks,

Nick

[MarqueeCrew · Accepted solution]

In use of the multi row formula you can group by the key and count the number of rows. That rowid can be checked against the days field and filtered out. Remember to keep the rows in date descending order.

Cheers,

Mark

[derekbelyea · Accepted solution]

Here is a different way to solve this.  Takes account of the fact that the days of the week that are weekend days both start with "S".

[BenMoss]

Interesting problem.

Here is how I have resolved.

Basiscally I used the formula ceil(([Days Taken]/5))*7 to work out the maximum number of days it could have taken to complete the job if we include weekends.

I then generate the dates for all of these look backs and filter out values where the part of the day is saturday or sunday. I then do a running total of days and exclude any values which is greater than the true number of days taken for the job.

See attached workflow.

Ben

[BenMoss]

Sorry I have inadvertently created pretty much the same solution mentioned by @MarqueeCrew! 

Anyway, the attached workflow to my post should put his vision into action!

Ben 

[nsmith293]

Thanks all!  This community is great!

I appreciate the help with this. I need to start learning the multi-row tool it would seem....

Thanks!