I found the netWorkDays function, however I don't think it applies to what I want to accomplish.
I want to track jobs with a due date of 7 business days after initiation. So if a job (record) is begun (created) on 9/1, I want the system to automatically calculate the due date that comes 7 business days after 9/1.
Does anyone know how to accomplish this?
Hello Givin ,
Yes i forgot you you need to add 7 working days ..
It's always cross 1 Saturday and 1 Sunday . So Instead you can add 9 directly and get the solution (No need any tough calculation . But if you need less then 7 days then we need some calculation)
Thanks
Hi Gavin -
Here's your solution:
Start by making your 5 fields as follows:
Original Date field: Nothing special. This is the 'driver' or 'input' field.
OriginalDayOfWeek field:
Select WkDaySerial field: ;
...Settings: Set Options 1,2,3,4,5
...Conditional Rules:
(etc...)
Algorithm for 7th Workday field:
Copy and paste this equation:
{Select WkDaySerial}==1? 9 : ({Select WkDaySerial})==2? 9: ({Select WkDaySerial})==3? 9: ({Select WkDaySerial})==4? 11: ({Select WkDaySerial})==5? 11: 0
Output Date (Due Date) field:
this is the result for 9/18/2019:
if you have more questions let me know!
Jonathan
432-978-4985
@Sunny - that formula counts all days, not just workdays.
@Julian - I suspect you may be correct, a set of nested "ifs" might be the most efficient way to go with this.
Hi Gavin
You're correct - netWorkDays is basically the inverse of what you need - it works out the number of work days between two dates.
I think what you need to achieve would be possible using some nested ifs - based on which day of the week the staring date is you can work out how many days to add for it to be 7 working days.
Julian
I can’t seem to see the images from this post anymore.
So I’ll post my solution here, for anyone else who needs it in future.
Thanks to @Callum.Boase for this solution.
The goal: add any number of work days to a start date.
We need 4 fields:
{Work days to add} + (ceil(({Work days to add}+(getDateDayOfWeek({Start date})-(ceil(getDateDayOfWeek({Start date})/6)-1)*6)-1)/5)-1)*2 + ceil(getDateDayOfWeek({Start date})/6-1)
{Start date}+{Days to add equation}
Known limitations:
Thanks @Malcolm-Optimi / @Callum.Boase, I needed this one today!
You say that this doesn’t work for subtracting days, however it appears it does!:
