I haven't fully tested it, but I think:
starting date - ( amount + Int ( amount / 5 ) * 2 + If ( Mod ( amount ; 5 ) + ( 7 - DayOfWeek ( starting date ) ) ≥ 7 ; 2 ) )
should do the trick
Thanks for the response.
I've gone ahead and tested your formula and ran across a couple instances where the calculated start date ends up as a Sunday. For Example, a due date of Monday, August 26 with a 1 working day requirement yields a result of Sunday, August 25 (see attached screen shot)
Just so I am clear on the formula, the variables I am using are:
Due Date = date entered by user
Amount = assumed number of working days to complete task
Start date = calculated result
Therefore the formula you provided would be modified as follows:
Start Date = Due Date - ( amount + Int ( amount / 5 ) * 2 + If ( Mod ( amount ; 5 ) + ( 7 - DayOfWeek ( Due Date ) ) ≥ 7 ; 2 ) )
I went ahead and re-created the formula above in excel and ran a data table varying due date days (Mon to Fri) with work day assumptions to see if I could identify a trend.
For the scenarios I found that were producing incorrect start dates, the conditional sum component all came out to the value of six (6). So I went ahead and changed the formula above to the version below (note change in red) and it now appears to work. Here's the revised calculation:
Start Date = Due Date - ( amount + Int ( amount / 5 ) * 2 + If ( Mod ( amount ; 5 ) + ( 7 - DayOfWeek ( Due Date ) ) ≥ 6 ; 2 ) )
Will it be a concern that a holiday might fall on this date interval and thus require pushing out the due date an additional number of days?
If so, you may need to approach this issue differently. We have a government mandated waiting period of 3 business days on certain transactions. I set my system to compute the number of business days off a calendar table where each record is a day on the calendar and each day we are open is marked with a certain common value. To compute a date for "3 business days from now" then become a process of simply matching to the 3rd open business day record of those records with the same date as today or later.