Nice work just using a formula in GSheets!
If you needed to do this within Make for any other reasons, there are at least two options, found here and here (both happen to be in the same thread).
To summarize yet another slightly different approach:
- You get a starting date
- Using a repeater, you create an array of dates that only land on a Mon-Fri (what you’ve defined as “working days”.)
- With your final array, you can use
get(array;n)to get the Nth weekday after the starting date.
For your repeater, it needs to repeat a sufficient number of times.
Since you’re looking for 5 “working” days you need to repeat 7 times to account for the possibility of the starting date landing on a Friday.
Let’s say you need 5 working days from Friday 7/5:
Dates that will be generated:
Saturday 7/6 (filtered out)
Sunday 7/7 (filtered out)
[1] Monday 7/8
[2]Tuesday 7/9
[3] Wednesday 7/10
[4] Thursday 7/11
[5] Friday 7/12
get(array;5) results in Friday 7/12
Taking it even further, if you had a custom list of non working days, like holidays that land on days during the week, you could query for that list (maybe from another Google Sheet), then add these to your filter.
Turn that list into another array, like “holidays”.
Using the same example… your filter defines what you keep, so you can use formatDate() on the date generated by the repeater to figure out the day of the week, keeping it if lands on Mon-Fri. Use an AND filter to keep the date only if it IS NOT in your holidays array.
Again, make sure your repeater is enough to cover the range, but don’t worry, with the Aggregator after the repeater you won’t be charged an Op for every repeat, it’s just 2 Ops regardless.
Hope this all made sense!