Adding Business Days to a Date to create New Date

[Former Member]

I am looking to add a formula to auto calculate a new date, but only want to count business Days.

Currently I am using formula to calculate how many days need to be added:

//ODD Priority Business Days

If left ({DEFECT.PRIORITY}, 1)= "1" then 10

else

If left ({DEFECT.PRIORITY}, 1)= "2" then 20

else

If left ({DEFECT.PRIORITY}, 1)= "3" then 50

else

If left ({DEFECT.PRIORITY}, 1)= "0" then 10

Now I need to create a formula to add these numbers to the date field, for conversation purposes, I need to add these business days to my "start date" to equil my "ODD date".

Example, if my "start date" is 1/1/2009 and it is a Priority 2, then I need to add 20 business days to that, so the "ODD date" would be: 1/29/2009.

Please help.

[Former Member · Accepted solution]

Try this if your formula name is @BusinessDays then

+@BusinessDays

this gives the date after adding business days.

Regards,

Raghavendra

[Former Member · Accepted solution]

When I try this, it only adds days to my date, it is including the weekends with it... I need a way to make it exclude the weekends.

[Former Member · Accepted solution]

Try this formula

{Startdate}-DateDiff("w",{Startdate},{Startdate}+@BusinessDays,crSaturday)-DateDiff("w",{Startdate},{Startdate}+@BusinessDays,crSunday)

Regards,

Raghavendra

[Former Member · Accepted solution]

I haven't tested it, but I think Ragavendra meant:

{Startdate} 
    + {@BusinessDays}
    + DateDiff("ww", {Startdate}, {Startdate} + {@BusinessDays}, crSaturday)
    + DateDiff("ww", {Startdate}, {Startdate} + {@BusinessDays}, crSunday)

[Former Member · Accepted solution]

Thank you Garrett Fitzgerald! Please see the modified formula which I meant

{Startdate} 
    + {@BusinessDays}
    - DateDiff("ww", {Startdate}, {Startdate} + {@BusinessDays}, crSaturday)
    - DateDiff("ww", {Startdate}, {Startdate} + {@BusinessDays}, crSunday)

Regards,

Raghavendra

[Former Member]

Actually it is giving me incorrect date, but it is only off by a few days... do you know what woudl cause this?

[Former Member]

can you give us the sample values to test at my end?

{Start Date}=?

@BusinessDays=?

Expected date=?

output of the formula=?

Regards,

Raghavendra

[Former Member]

Thank you, it works perfectly!