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

    AdamWood

      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

               Hi Adam,

               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
            AdamWood

                 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
              AdamWood

                   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
                philmodjunk

                     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.