+ Start a Discussion
Coral RacingCoral Racing 

Validation Rule Help with a DATE / Time

Hello,

I have a date time field that users manually select for when they want to implement a Change but I want to limit their selections to only Monday, Wednesdays and Fridays of each week.

Can anyone help?
Many Thanks

Sonya
vibhor goelvibhor goel
Hi Sonya,

I am not sure if you are looking for the full logic of the validation Rule or just the way to get the Day of the week in validation.

In case you are trying to find a way to get the days then,
Please use this link:
https://help.salesforce.com/HTViewHelpDoc?id=formula_examples_dates.htm&language=en_US#getting-dmy-from-date

Here under the section - 'Finding and Displaying the Day of the Week From a Date' you can find a way to do so.

Hope it helps!!
If you still need more help you can tell.

Regards,
Vibhor
 
Coral RacingCoral Racing
Hi Thanks, I did see this before, but what I am looking for is a validation rule that throws an error if the User selects a date that does not fall on a Monday , Wednesday or Friday. Thanks Sonya
Vinoth Vijaya BaskerVinoth Vijaya Basker
Hi, 

Please find the below validation rule,

NOT(OR(MOD( TestDate__c - DATE(1900, 2, 3), 7) = 2, MOD( TestDate__c - DATE(1900, 2, 3), 7) = 4, MOD(TestDate__c - DATE(1900, 2, 3), 7) = 6))

Replace TestDate__c  with your date field. 


Thanks,
Vinoth
Coral RacingCoral Racing
Thanks both, really quick responses.  I will try both methods

Much apprciated
Vinoth Vijaya BaskerVinoth Vijaya Basker
Hi, 

I have made few changes for Date Time field. Please find the below formula. 

NOT(OR(MOD(  DATEVALUE(TestDate__c)  - DATE(1900, 2, 3), 7) = 2, MOD( DATEVALUE(TestDate__c) - DATE(1900, 2, 3), 7) = 4, MOD( DATEVALUE(TestDate__c) - DATE(1900, 2, 3), 7) = 6))


Thanks,
Vinoth
Coral RacingCoral Racing
Thanks for this. I tested the conditional formula and I am getting the following error Insert Operator Error: Incorrect argument type for function 'DATEVALUE()'.
Coral RacingCoral Racing
Apologies, it would appear my field is Date and not Date/Time so the formula below does not work as not a datetime value


NOT(OR(MOD(  DATEVALUE(TestDate__c)  - DATE(1900, 2, 3), 7) = 2, MOD( DATEVALUE(TestDate__c) - DATE(1900, 2, 3), 7) = 4, MOD( DATEVALUE(TestDate__c) - DATE(1900, 2, 3), 7) = 6))
Coral RacingCoral Racing
I changed it to this

NOT(
OR
(MOD((CAB_Date__c) - DATE(1900, 2, 3), 7) = 2,
MOD((CAB_Date__c) - DATE(1900, 2, 3), 7) = 4,
MOD((CAB_Date__c) - DATE(1900, 2, 3), 7) = 6)
)