function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
kmunsonkmunson 

CloseDate + 60 (Conditional)

Using the CloseDate + 60 formula, what is the Syntax for adding that the date must land on the nearest weekday? In other words, if 60 plus the close date results in a Sunday (Date), how do do I get the result to be Monday? 

NerdFishNerdFish

Now you have my curiosity going.  I didn't hash it out to see what your specific formula might look like in the end but I found this in help.  I think it might point you in the right direction:

 

MOD

Description:Returns a remainder after a number is divided by a specified divisor.
Use:MOD(number, divisor)and replace number with the field or expression you want divided; replace divisor with the number to use as the divisor.
Example:MOD(3, 3)returns 0

MOD(4, 3)returns 1

MOD(123, 100)returns 23

You may want to prevent users from scheduling meetings on a Saturday or Sunday. Use the following example to apply a validation rule to a custom date field calledMy Date.

CASE(MOD(My_Date__c - DATE(1900, 1, 7), 7),
0, 0,
6, 0,
1) = 0

This example displays the following error message when the value of My Date is not Monday through Friday: “My Date is not a weekday.”

kmunsonkmunson

Thanks again for the response. I'm not having luck with this formula. The use case is as follow. Salesperson forecasts a close date on an opportunity of XX/XX/XX which drive a Estimated Shippment Date (i.e., CloseDate + 60 days). We'd like the Est. Shipment Date formula to produce a date which is not a Saturday or Sunday. 

NerdFishNerdFish

OK, this may not be the best solution but it works.

First, Mod() is a little picky so you will have to first create a field for your closed_date+60.  Then you will have to use that field in your formula.  It will actually make the formula a little easier to work because it can get kind of cumbersome.  In this example, I created the field closedpluss__c.

 

So then you will need to figure out which day of the week that falls on.  That's where the mod function works.  The function will look like this:

 

MOD(closedpluss__c-DATE(1900,1,7),7)  This takes your date, subtracts 1-7-1900 to "zero" the days, divides that number by 7 and then returns the remainder.  That formula will return 1 for sunday, 2 for monday, etc.. and 0 for Saturday (the 7th day).

 

next, you'll need to run some logical tests using IF(test, true, false).  So:

IF( MOD(closedpluss__c-DATE(1900,1,7),7)=0,closedpluss__c + 2, closedpluss__c)

 

What that does is test your mod formula and if the result is 0 (saturday), it adds 2 days to your date, otherwise it returns the date unmodified.  But, since you need to test for sunday too, you need a second if:

IF( MOD(closedpluss__c-DATE(1900,1,7),7)=1,closedpluss__c + 1, closedpluss__c)

 

Then take this second if statement and insert into the "true" argument of the first statement.  That yields the lovely mess:

IF(MOD(closedpluss__c-DATE(1900,1,7),7)=1,closedpluss__c+1,IF(MOD(closedpluss__c-DATE(1900,1,7),7)=0,closedpluss__c+2,closedpluss__c))

 

In basic terms what that function does is say if our date lands on Sunday, add one day; if our date lands on Saturday, add two days, and if our date lands on any other day, use it as is.

kmunsonkmunson

Thnks for the thoughtful reply. I appreciate the assistance.