Formula to Add/Subtract Business Days (Weekdays) to/from a Date

Version 2

    Over the past couple of months, I have seen a few questions about how to calculate a date based on adding or subtracting a certain number of business days from another date. I may have missed it, but I don't recall seeing a concise solution. After some research, I found some formulas that do this, and I have adapted them for Tableau. I tested the results in a variety of scenarios - both adding business days to a date and subtracting business days from a date, and it appears to work in all of my testing. I wanted to share it in case it helps others, as well. Below is the formula, and I have attached a packaged workbook with the calculations. Please let me know if you have any feedback. Thanks.

     

     

     

    IF [Biz Days Offset]>0 THEN

     

     

        DATE(CASE DATEPART('weekday',[Date Calc basis])

            WHEN 1 THEN DATEADD('day',[Biz Days Offset]+FLOOR(([Biz Days Offset]-1)/5)*2,[Date Calc basis])

            WHEN 2 THEN DATEADD('day',[Biz Days Offset]+FLOOR(([Biz Days Offset])/5)*2,[Date Calc basis])

            WHEN 3 THEN DATEADD('day',[Biz Days Offset]+FLOOR(([Biz Days Offset]+1)/5)*2,[Date Calc basis])

            WHEN 4 THEN DATEADD('day',[Biz Days Offset]+FLOOR(([Biz Days Offset]+2)/5)*2,[Date Calc basis])

            WHEN 5 THEN DATEADD('day',[Biz Days Offset]+FLOOR(([Biz Days Offset]+3)/5)*2,[Date Calc basis])

            WHEN 6 THEN DATEADD('day',[Biz Days Offset]+CEILING(([Biz Days Offset])/5)*2,[Date Calc basis])

            WHEN 7 THEN DATEADD('day', -1+[Biz Days Offset]+CEILING(([Biz Days Offset])/5)*2,[Date Calc basis])

        END)

     

     

    ELSEIF [Biz Days Offset]<0 THEN

     

     

        DATE(CASE DATEPART('weekday',[Date Calc basis])

            WHEN 7 THEN DATEADD('day',-(ABS([Biz Days Offset])+FLOOR((ABS([Biz Days Offset])-1)/5)*2),[Date Calc basis])

            WHEN 6 THEN DATEADD('day',-(ABS([Biz Days Offset])+FLOOR((ABS([Biz Days Offset]))/5)*2),[Date Calc basis])

            WHEN 5 THEN DATEADD('day',-(ABS([Biz Days Offset])+FLOOR((ABS([Biz Days Offset])+1)/5)*2),[Date Calc basis])

            WHEN 4 THEN DATEADD('day',-(ABS([Biz Days Offset])+FLOOR((ABS([Biz Days Offset])+2)/5)*2),[Date Calc basis])

            WHEN 3 THEN DATEADD('day',-(ABS([Biz Days Offset])+FLOOR((ABS([Biz Days Offset])+3)/5)*2),[Date Calc basis])

            WHEN 2 THEN DATEADD('day',-(ABS([Biz Days Offset])+CEILING((ABS([Biz Days Offset]))/5)*2),[Date Calc basis])

            WHEN 1 THEN DATEADD('day',1-(ABS([Biz Days Offset])+CEILING((ABS([Biz Days Offset]))/5)*2),[Date Calc basis])

        END)

     

     

    ELSE [Date Calc basis]

    END