function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
VamsiVamsi 

validates that the custom date field is a weekday (not Saturday or Sunday).

Hi,

Can someone please explain me how the below formula works(Not sure why they have particulary used 1900-1-7  and how the case function works ).

Validates that the value of a custom date field is a weekday (not Saturday or Sunday).
Formula:  CASE(MOD( My_Date__c - DATE(1900, 1, 7), 7), 0, 0, 6, 0, 1) = 0
Error Message:    Date must be a weekday.
Error Location:    My Date
Becka DBecka D
The formula works by picking a known Saturday. I've used 1900-1-6 in the past, as that is a known Saturday date. Then it subtractes that date from the provided date, and using MOD to divide the result by 7 and returns the remainder. The CASE function works by looking at a value of a given field and based on that value, giving an output. I do think your validation rule needs a bit of work before it will actually work since you don't appear to be using the CASE function correctly.

Some like this should be closer to working:
CASE(MOD(My_Date__c - DATE(1900, 1, 6), 7), 0, false, 1, false, 2, true, 3, true, 4, true, 5, true, 6, true,true)
Anup Padakandla 56Anup Padakandla 56
This formula worked for me. Thanks Becka. You made my day with your valuale tip.
CASE(MOD([Opportunity].CloseDate+180 - DATE(1900,1,6),7),0,[Opportunity].CloseDate+181,6,[Opportunity].CloseDate+182, [Opportunity].CloseDate+180)