You need to sign in to do that
Don't have an account?
kmunson
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?
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
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.
This example displays the following error message when the value of My Date is not Monday through Friday: “My Date is not a weekday.”
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.
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.
Thnks for the thoughtful reply. I appreciate the assistance.