+ Start a Discussion
shoba shobashoba shoba 

validation rule for an opportunity closed date

Hi my scenario is to restrict the user,if they try to edit the opportunity record after the three business days from the ClosedDate field. For example : if i update the opportunity after three days from the ClosedDate(5/12/2016), it will through an alart message, then if i edit the ClosedDate as 5/13/2016 then the record will save. But by requirement is to restrict the update operation itself. 
(CASE( 
MOD( CloseDate - DATE(1900, 1, 7), 7), 
0, CloseDate+3, 
1, CloseDate+3, 
2, CloseDate+3, 
3, CloseDate+5, 
4, CloseDate+5, 
5, CloseDate+5, 
6, CloseDate+4, 

CloseDate))<=TODAY()

 
Best Answer chosen by shoba shoba
MellowRenMellowRen
Hi

This should give you 3 days without the ability to change the date as per Ch Nagendra Prasad’s simplified example:
NOT(ISNEW()) && (TODAY()-PRIORVALUE(CloseDate) >= 3)
Or applying it to your original business day example:
NOT(ISNEW()) &&
 (CASE(MOD(PRIORVALUE(CloseDate) - DATE(1900, 1, 7), 7),
  0, PRIORVALUE(CloseDate)+3,
  1, PRIORVALUE(CloseDate)+3,
  2, PRIORVALUE(CloseDate)+3,
  3, PRIORVALUE(CloseDate)+5,
  4, PRIORVALUE(CloseDate)+5,
  5, PRIORVALUE(CloseDate)+5,
  6, PRIORVALUE(CloseDate)+4,
 PRIORVALUE(CloseDate)))<=TODAY()
Haven’t tested it but this should do it.

Good luck.

All Answers

Nagendra Prasad ChNagendra Prasad Ch
Hi Shoba,
Try this, It will prevent edit operation itself.
 
NOT(ISNEW()) && (TODAY() > CloseDate) && (TODAY()-CloseDate >= 3)

 
shoba shobashoba shoba

Hi Ch Nagendra Prasad

Thanks for your reply

Actually if i try to edit the CloseDate field bu future date,then its not showing an alert messagevsimply record get saved.

MellowRenMellowRen
Hi

This should give you 3 days without the ability to change the date as per Ch Nagendra Prasad’s simplified example:
NOT(ISNEW()) && (TODAY()-PRIORVALUE(CloseDate) >= 3)
Or applying it to your original business day example:
NOT(ISNEW()) &&
 (CASE(MOD(PRIORVALUE(CloseDate) - DATE(1900, 1, 7), 7),
  0, PRIORVALUE(CloseDate)+3,
  1, PRIORVALUE(CloseDate)+3,
  2, PRIORVALUE(CloseDate)+3,
  3, PRIORVALUE(CloseDate)+5,
  4, PRIORVALUE(CloseDate)+5,
  5, PRIORVALUE(CloseDate)+5,
  6, PRIORVALUE(CloseDate)+4,
 PRIORVALUE(CloseDate)))<=TODAY()
Haven’t tested it but this should do it.

Good luck.
This was selected as the best answer
mritzimritzi
Following validation formula will restrcit users if today's date is more than 3 business days from closeDate

paste it in Error Formula box:
(
(5 * ( FLOOR( ( Today() - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( TODAY() - DATE( 1900, 1, 8), 7 ) ) )
-
(5 * ( FLOOR( ( CloseDate- DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( CloseDate - DATE( 1900, 1, 8), 7 ) ) ) 
) > 3

If this helps you out, mark this Best Answer