5 Replies Latest reply on Jul 5, 2018 11:34 AM by Alex Braun

    Calculate End Date from Start Date and # of Working Days

    David Salinas

      Hello,

       

      I am trying to create a calculated field that gives the due date of a project, given the Start Date, and the # of working days allowed to complete the item.

       

      Any help would be greatly appreciated!

       

       

       

      Thanks

        • 1. Re: Calculate End Date from Start Date and # of Working Days
          Deepak Rai

          End date should be like this:

           

          DATEADD('day',No of Working Days, Start Date)

          • 2. Re: Calculate End Date from Start Date and # of Working Days
            David Salinas

            I had that first, but realized it is counting the weekends as well. I need it to count only the number of working days, or business days.

             

            So if the start date is 5/24/18, and the # of working days is 10, I should get 6/7/18.

             

            I am currently getting 6/3/18

            • 3. Re: Calculate End Date from Start Date and # of Working Days
              Alex Braun

              You may have to figure out how many weekends are in there too.  For instance if you start on a tuesday 7/10 and had 10 working days, with 5 working days a week, but since you are starting on tuesday, you would need to get the datepart('weekday',[date]) which equals 3, take 6 (friday) - 3 to get 3 working days that week.  Then take your 10 working days - 3 days this week = 7 days, then take 7 days / 5 working days a week which gives you 1.4 rounded down to 1, so add 2 days for the weekend. Your final equation would look like this:

               

              DATEADD(

                  'day',

                  [Working Days]+(ROUND(([Working Days]-(6-DATEPART('weekday',[DateTime])))/5,0)*2),

                  [DateTime]

              )

              • 4. Re: Calculate End Date from Start Date and # of Working Days
                David Salinas

                I found this solution, and it worked, until I found another issue needing to be addressed.

                 

                 

                 

                 

                 

                 

                IF [WorkDays]>0 THEN

                 

                 

                  
                DATE(CASE DATEPART('weekday',[StartDate])

                        WHEN 1 THEN DATEADD('day',[WorkDays]+FLOOR(([WorkDays]-1)/5)*2,[StartDate])

                        WHEN 2 THEN DATEADD('day',[WorkDays]+FLOOR(([WorkDays])/5)*2,[StartDate])

                        WHEN 3 THEN DATEADD('day',[WorkDays]+FLOOR(([WorkDays]+1)/5)*2,[StartDate])

                      
                WHEN 4 THEN DATEADD('day',[WorkDays]+FLOOR(([WorkDays]+2)/5)*2,[StartDate])

                        WHEN 5 THEN DATEADD('day',[WorkDays]+FLOOR(([WorkDays]+3)/5)*2,[StartDate])

                        WHEN 6 THEN DATEADD('day',[WorkDays]+CEILING(([WorkDays])/5)*2,[StartDate])

                      
                WHEN 7 THEN DATEADD('day', -1+[WorkDays]+CEILING(([WorkDays])/5)*2,[StartDate])

                  
                END)

                 

                 

                ELSE [StartDate]

                END

                 

                 

                 

                 

                 

                 

                 

                 

                 

                 

                 

                This gave me the correct number of working days, but I realize now I need to account for Holidays.

                • 5. Re: Calculate End Date from Start Date and # of Working Days
                  Alex Braun

                  Take a look at my calculation, its quite a bit cleaner, as far as holiday's go, you could blend on a list of holidays and say

                  dateadd('day',

                  if [Holiday Date] >= [Start Date] and [Holiday] <= [End Date] then 1 else 0,

                  [End Date])