4 Replies Latest reply on Aug 2, 2013 3:21 PM by philmodjunk

# Calculate Start Date from known Due Date based on number of working days

### Title

Calculate Start Date from known Due Date based on number of working days

### Post

While I have found several posts that cover the calculation of an end date based on a given starting date and the number of working days (below is a link to a post with a solution), I am attempting to do the opposite - calculate a start date given a known due date and the number of working days to complete a task.

Any suggestions on how to perform such a calculation (or modify the calculation developed in the below link) would be appreciated

• ###### 1. Re: Calculate Start Date from known Due Date based on number of working days

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

regards, Menno

• ###### 2. Re: Calculate Start Date from known Due Date based on number of working days

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 ) )

• ###### 3. Re: Calculate Start Date from known Due Date based on number of working days

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 ) )

• ###### 4. Re: Calculate Start Date from known Due Date based on number of working days

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.