Hello
I have a google sheet that automatically gets populated from a jotform form. I would like to add 5 working days to the submision date of the form. I am strugling to find a way to do it
Any help will be greatly appreciated.
Welcome to the Make community!
You can use the built-in function addDays.
For further assistance, please provide the following:
1. Screenshots of module fields and filters
Please share screenshots of relevant module fields and filters in question? It would really help other community members to see what you’re looking at.
You can upload images here using the Upload icon in the text editor:
2. Scenario blueprint
Please export the scenario blueprint file to allow others to view the mappings and settings. At the bottom of the scenario editor, you can click on the three dots to find the Export Blueprint menu item.
(Note: Exporting your scenario will not include private information or keys to your connections)
Uploading it here will look like this:
blueprint.json (12.3 KB)
3. And most importantly, Output bundles
Please provide the output bundles of the modules by running the scenario, then click the white speech bubble on the top-right of each module and select “Download output bundles”.
A.
Save the bundle contents in your text editor as a bundle.txt file, and upload it here into this discussion thread.
Uploading it here will look like this:
bundle.txt (12.3 KB)
B.
If you are unable to upload files on this forum, alternatively you can paste the formatted output bundle in this manner:
-
Either add three backticks
```before and after the code, like this:```
input/output bundle content goes here
``` -
Or use the format code button in the editor:
Providing the output bundles will allow others to replicate what is going on in the scenario even if they do not use the external service.
Following these steps will allow others to assist you here. Thanks!
samliew – request private consultation
Join the unofficial Make Discord server to chat with us!
1 Like
Thanks Samliew
I do not have a scenario that is working. I just have a trigger setup to monitor new rows into a google sheet
I want to then process the info from one of the cells in the new row which is a date.
You can use the built-in function addDays
This will not work as I would like to add business days. I need to exclude weekends from the additional days.
I am trying to process the info from a cell in the google sheet
Thanks
Something like this perhaps?
(assuming the date is in column C)
samliew – request private consultation
Join the Make Fans Discord server to chat with other makers!
Thanks again
It is my understanding that the
addDays (42.date ; 5)
will add 5 days to the date
So if the initial date falls on a friday, the solution will add 5 days to friday, which will mean the date will change to the following week wednesday.
That is only three business days.
The solution needs to take into account the weekends and subtract any weekend from the calculation.
Assuming I want to add 3 business days to the date, and the date falls on a monday. the result will need to be the same week Thursday. But if the day falls on a Friday, the day will be the following week wednesday.
I managed to do it in Zapier using custom code.
There is a google sheet function that does it.
Maybe I can append to the row that is added. Add the google sheet function and pass the date to it.
If you think it will work pls let me know
Thanks
=WORKDAY(start_date, num_days, [holidays])
Do you think the idea will work?
Ah, so you meant add 7 days.
Just add 7 days, it will include 5 working days. It’s a simple as that.
No point complicating everything.
samliew – request private consultation
Join the Make Fans Discord server to chat with other makers!
I managed to get it to work using the Workday function
I was not overcomplicating.
It needed to be different for different due dates.
cant just add 2 extra days, as it does not work for 3 days as I described
1 Like
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!
2 Likes
Thanks Donald
Much appreciated
I did not think of trying it your way.
A bit difficult for me to follow. Still new to Make.com
Will keep it in mind for future reference.
I am however having a problem when trying to run a complete scenario.
Th update row works when running the module individually. However does not work when running the complete scenario.
I hope the below attachment help
blueprint.json (35.6 KB)
Thanks