+ Start a Discussion
brielea1984brielea1984 

Validation Rule help - date cannot be past the 5h of the next month

I have a date field on Object__c called Contact_Date__c. The user cannot create an Object__c record where Contact_Date__c can only be the current month unless it is for the previous month and today's date is before the 5th.

 

Basically, an Object__c record for November (Contact_Date__c) must be created no later than December 5th.

 

I played around with AND/OR statments but am having difficulty. I was able to create the validation rule for having Today's month and Contact month the same: 

 

MONTH( Today() ) <> MONTH( hhs__Contact_Date__c )

 

but I need to somehow write in an exception of UNLESS, Month(Today()) is Month(hhs__Contact_Date__c)+1 and Day(Today()) < 5. 

 

Any ideas?

Best Answer chosen by Admin (Salesforce Developers) 
harsha__charsha__c

Hey,

 

Good one..!

 

But I think in case JAN month the rule may give some wrong calculations

 

I mean, MONTH( TODAY() ) -1 will return 0 for the JAN month

 

Keeping this case in the mind I made the following rule, which also doesn't need for the two seperate Rules...I checked it in my org, working fine as you required

 

Try this once and let me in case of  any concerns/issues..!

 

 

( MONTH( Contact_Date__c ) = MONTH( TODAY() ) && DAY(Contact_Date__c ) > 5 ) || (MONTH( Contact_Date__c ) <> MONTH( TODAY() ) && MONTH( Contact_Date__c ) <> IF(MONTH( TODAY() ) = 1 , 12, MONTH( TODAY() ) -1) )

 

All Answers

RamitRamit

Hi,

 

The scenario was not entirely clear from the description given by you. Based on my understanding of your question it seems that you want to make sure that Creation Date of record should be less than the 5th of Month next to Contact_Date__c. If this is what you are looking for then, please see the formula below :

 

IF( 
DATEVALUE(CreatedDate) <Date( 
If( 
Month(Contact_Date__c)=12, 
YEAR(Contact_Date__c)+1, 
YEAR(Contact_Date__c) 
) 
, 
If( 
Month(Contact_Date__c)=12, 
1, 
Month(Contact_Date__c)+1 
) 
, 
05 
), 
True, 
False 
)

 

Let me know in case you are looking for something else.

 

 

harsha__charsha__c
MONTH( hhs__Contact_Date__c) <> MONTH( TODAY() ) && (MONTH( hhs__Contact_Date__c) <> MONTH( TODAY() ) +1 && DAY(hhs__Contact_Date__c) > 5)
brielea1984brielea1984

Hi everyone,

 

Thank you for your help. Neither solution worked, and I think it's because we're looking for two things. I wound up making two validaton rules, with help from your formulas, and have found that together they work perfectly. I'm able to create a note for today and November 1, but not Oct 31. I'll have to test it on Dec. 6 to make sure it still works. But in the meantime, for your reference, here is what I did:

 

Validation Rule 1:

MONTH( hhs__Contact_Date__c) <> MONTH( TODAY() ) && (MONTH( hhs__Contact_Date__c) <> MONTH( TODAY() ) -1)

 

Validation Rule 2:

MONTH( hhs__Contact_Date__c) <> MONTH( TODAY() ) && (DAY(TODAY()) >5)

 

Thanks again!

harsha__charsha__c

Hey,

 

Good one..!

 

But I think in case JAN month the rule may give some wrong calculations

 

I mean, MONTH( TODAY() ) -1 will return 0 for the JAN month

 

Keeping this case in the mind I made the following rule, which also doesn't need for the two seperate Rules...I checked it in my org, working fine as you required

 

Try this once and let me in case of  any concerns/issues..!

 

 

( MONTH( Contact_Date__c ) = MONTH( TODAY() ) && DAY(Contact_Date__c ) > 5 ) || (MONTH( Contact_Date__c ) <> MONTH( TODAY() ) && MONTH( Contact_Date__c ) <> IF(MONTH( TODAY() ) = 1 , 12, MONTH( TODAY() ) -1) )

 

This was selected as the best answer
brielea1984brielea1984

Ahhhh, great catch on the January situation! I tried it out and it works! I'll test it again in 2 days, but so far looks good!

 

Thanks so much!