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
Ross McLeod 3Ross McLeod 3 

Two dates within same week

Hi

I have a requirement to create a validation rule which validates a start and end date are within the same week.

* Week is classed as Monday to Sunday
* Both dates must fall within the same week

I'm not sure how to accomplish this as I believe that Salesforce week os Sunday to Saturday.

Thanks
ANUTEJANUTEJ (Salesforce Developers) 
Hi Ross,

>> https://developer.salesforce.com/forums/?id=906F0000000MNWhIAO

I think the above link has a similar implementation that you can try checking once.

I am adding the mentioned best answer for quick reference:
  
IF( ISPICKVAL( PlanFor__c , "Week") , 
((EndDate__c - StartDate__c)>=7) , 
((EndDate__c - StartDate__c)>=30)
)

Let me know if it helps you and close your query by marking it as solved so that it can help others in the future.  

Thanks.
Suraj Tripathi 47Suraj Tripathi 47

Hi Ross,

Please find the solution.

Go to objectManager ==>choose your object ==> choose Validation rule

(End_Date__c - Start_Date__c)<8

Put your  error message : Same week

Please do the needful changes.

Please mark it as the best Answer

Thank You

 
mukesh guptamukesh gupta
Hi Ross, 

Please follow below code in validation rule:
  
if(MOD( FLOOR ( ( Start_Date__c - TODAY() ) / 7 ), 52) == MOD( FLOOR ( ( End_Date__c  - TODAY() ) / 7 ), 52),true, false)

if you need any assistanse, Please let me know!!


Kindly mark my solution as the best answer if it helps you.

Thanks
Mukesh
 
Andrew GAndrew G
No one wants to play with WEEKDAY()

If we use: 
(End_Date__c - Start_Date__c)<8
we just find out that the dates are less than 7 days apart , which could be a Thursday Start Date to the Next Tuesday End date.

But if we try: 
AND(
  (End_Date__c - Start_Date__c)<8,
  WEEKDAY(End_Date__c) > WEEKDAY(Start_Date__c)
)
so the two days are 7 Days apart and in the Same week, assuming Sunday to Saturday.

Now to handle the Monday start versus Sunday Start. 
AND(
  (End_Date__c - Start_Date__c)<8,
  MOD(WEEKDAY(End_Date__c)+6,7) > MOD(WEEKDAY(Start_Date__c)+6,7)
)
for explanation of  
MOD(WEEKDAY(End_Date__c)+6,7)
WEEKDAY will return a value between 1 and 7 matching the day of the week.  1 being Sunday thru to 7 being Saturday.
but we need to work Monday as 1 thru to Sunday as 7
so we add 6 to the weekday result, divide by 7 and get the Modulo (MOD - Returns a remainder after a number is divided by a specified divisor.)

this resets the number to being Sunday as a 7, hence a higher number than Monday as a 1.

hope that throws some light on things.

regards
Andrew

 
Ross McLeod 3Ross McLeod 3
Thanks Andrew G this was almost what I need but it isn't quite working. I have tried the following:

Start Date: 10/05/2021 (Monday) - End Date 11/05/2021 (Tuesday) Result: Validation rule shows
Start Date: 10/05/2021 (Monday) - End Date 14/05/2021 (Friday) Result: Validation rule shows
Start Date: 10/05/2021 (Monday) - End Date 15/05/2021 (Saturday) Result: Validation rule shows
Start Date: 10/05/2021 (Monday) - End Date 17/05/2021 (Sunday) Result: No rule shows
Start Date: 10/05/2021 (Monday) - End Date 18/05/2021 (Monday) Result: No rule shows

Any ideas?
Andrew GAndrew G
Apologies, the vagaries of the MOD
Try 
AND(
  (End_Date__c - Start_Date__c)<8,
  MOD(WEEKDAY(End_Date__c)+5,7) > MOD(WEEKDAY(Start_Date__c)+5,7)
)
adjust the +6 to +5

cheers
Andrew

 
Ross McLeod 3Ross McLeod 3
Thanks for the response, sadly this is still not right, but I think I may not have been clear. The example dates that I tried the Tuesday, Friday, Saturday dates should be valid as they are in the same week. The only one that isn't is Monday 18th May as this is where the start date and end date are not in the same week.

So if the start date is Monday 10/05/2021 then the end date must be Less than or equeal to Sunday 16/05/2021.

Thanks for your patients. 
 
Andrew GAndrew G
Hi Ross
I have created a formula field with the following code: 
IF( 
  AND(
    ( AJG567__Date_2__c -  AJG567__Date_1__c )<8,
    MOD(WEEKDAY( AJG567__Date_2__c )+5,7) > MOD(WEEKDAY( AJG567__Date_1__c )+5,7)
  ),
  "Same Week",
  "Not Same Week"
)
And here is a shot of the page that is displayed - it includes the "adjust" fields which i used to test the calculation of the numbers
User-added image
I'm unsure why you aren't getting similar results.

regards
Andrew