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
Phani Pydimarry 4Phani Pydimarry 4 

Compare two dates in two different months with a time frame on Salesforce

I have a scenario where I have two Dates on Account.
Our partners move up to a level above if they buy a product in this month. But the data coming to us shows they moved up in the next month (1st to 10th) as they are processed in this time frame.
For Example
Date1: Joining Date Say 5/12/2017 MovedupDate: Moved up date Say 4/1/2018
I would like to create a flag on Account which say if the Partner moved up to a level within the same Month or the next month 1st to 10th it would come up as 'YES' or '1'. Please help me with a solution to accomplish this. My first thoughts are Process builder.
Best Answer chosen by Phani Pydimarry 4
Saravana Muthu 8Saravana Muthu 8
Please use this one.

Data TypeFormula  
AND(YEAR( Movedup_date__c ) = YEAR( Joining_date__c),MONTH(Movedup_date__c ) = MONTH( Joining_date__c)) 

|| 

AND(YEAR( Movedup_date__c) = YEAR( Joining_date__c ),MONTH(Movedup_date__c ) = (MONTH( Joining_date__c)+1),DAY(Movedup_date__c) <= 10) 
|| 
AND(YEAR( Movedup_date__c) = (YEAR( Joining_date__c)+1),MONTH(Movedup_date__c ) = (MONTH( Joining_date__c)-11),DAY(Movedup_date__c) <= 10)

Please let me know if it works.

Thanks,
Saravana

All Answers

Saravana Muthu 8Saravana Muthu 8
Hi,

You can create a formula checkbox if the moved up date is within the same month.

AND(YEAR( Moved up date) = YEAR( Joining Date),MONTH( Moved up date ) = MONTH( Joining Date ))


Please let me know if it helps.

Please don't forget to mark this as solved if it's resolved.

Thanks,
Saravana
Phani Pydimarry 4Phani Pydimarry 4
Thanks but the moved up date is 10 days of next month.. Example if the joining date is 5/12/17 the moved up date is 4/1/18

Thanks
Saravana Muthu 8Saravana Muthu 8
Please use the below formula.


AND(YEAR( Moved up date) = YEAR( Joining Date),MONTH( Moved up date ) = MONTH( Joining Date ))
|| 

AND(YEAR( Moved up date ) = YEAR( Joining Date ),MONTH( Moved up date ) <> MONTH( Joining Date ),DAY(Moved up date) <= 10)

Thanks,
Saravana
Phani Pydimarry 4Phani Pydimarry 4
Thanks so much @saravana 
Phani Pydimarry 4Phani Pydimarry 4
Hi Saravana... The flag is also considering Move Ups for next to next month. For example People who joined in October and move up within 10 days of December was also considered 
Saravana Muthu 8Saravana Muthu 8
Can you please explain with a date?

Thanks,
Saravana
Saravana Muthu 8Saravana Muthu 8
Please use the below formula and let me know if it works.

AND(YEAR( Moved up date) = YEAR( Joining Date),MONTH( Moved up date ) = MONTH( Joining Date ))
|| 

AND(YEAR( Moved up date ) = YEAR( Joining Date ),MONTH( Moved up date ) = MONTH( Joining Date )+1,DAY(Moved up date) <= 10)

Thanks,
Saravana
Phani Pydimarry 4Phani Pydimarry 4
Expected Result
If the Partner enrolled in 10/10/2017 and Moved up in 10/11/2017 (or from 1 to 10 of November) then the flag should be Yes

Preseant Result 

If the Partner enrolled in 10/10/2017 and Moved up in 10/12/2017 the result is showing up as True but this should not be the case

Thanks for coming back
Phani Pydimarry 4Phani Pydimarry 4
I am still getting the December data I am afraid. 
Saravana Muthu 8Saravana Muthu 8
Hi,

Sorry slight changes in the formula use this one.

Data TypeFormula  
AND(YEAR( Movedup_date__c ) = YEAR( Joining_date__c),MONTH(Movedup_date__c ) = MONTH( Joining_date__c)) 

|| 

AND(YEAR( Movedup_date__c) = YEAR( Joining_date__c ),MONTH(Movedup_date__c ) = (MONTH( Joining_date__c)+1),DAY(Movedup_date__c) <= 10)

Thanks,
Saravana
Saravana Muthu 8Saravana Muthu 8
Please let me know if it works for you.

Thanks,
Saravana
Phani Pydimarry 4Phani Pydimarry 4
Hi this logic is jumping +1. For example If the joining date is in October, it ignores November and Jumps to December I am afraid. 
Saravana Muthu 8Saravana Muthu 8
Can you please provide the formula you have used in your org formula field?

Thanks,
Saravana
Phani Pydimarry 4Phani Pydimarry 4
AND(YEAR( Assistant_Supervisor_Date__c) = YEAR( Enrollment_Date_Formula__c),MONTH( Assistant_Supervisor_Date__c ) = MONTH( Enrollment_Date_Formula__c )) 
|| AND(YEAR( Assistant_Supervisor_Date__c ) = YEAR( Enrollment_Date_Formula__c ),MONTH( Assistant_Supervisor_Date__c ) <> (MONTH(Enrollment_Date_Formula__c)+1),DAY(Assistant_Supervisor_Date__c) <= 10)
Saravana Muthu 8Saravana Muthu 8
Hi,

You have used <> which is not equals to but I have checkrd if both month are equals.
Changed formula.

AND(YEAR( Assistant_Supervisor_Date__c) = YEAR( Enrollment_Date_Formula__c),MONTH( Assistant_Supervisor_Date__c ) = MONTH( Enrollment_Date_Formula__c )) 
|| AND(YEAR( Assistant_Supervisor_Date__c ) = YEAR( Enrollment_Date_Formula__c ),MONTH(Assistant_Supervisor_Date__c ) = (MONTH(Enrollment_Date_Formula__c)+1),DAY(Assistant_Supervisor_Date__c) <= 10) 

Let me know if it works as you expected.

Thanks,
Saravana
Saravana Muthu 8Saravana Muthu 8
Hi,

You have used <> which is not equals to but I have checked if both month are equals.

Changed formula.

AND(YEAR( Assistant_Supervisor_Date__c) = YEAR( Enrollment_Date_Formula__c),MONTH( Assistant_Supervisor_Date__c ) = MONTH( Enrollment_Date_Formula__c )) 
|| AND(YEAR( Assistant_Supervisor_Date__c ) = YEAR( Enrollment_Date_Formula__c ),MONTH(Assistant_Supervisor_Date__c ) = (MONTH(Enrollment_Date_Formula__c)+1),DAY(Assistant_Supervisor_Date__c) <= 10) 

Let me know if it works as you expected.
​
Thanks,
Saravana
Phani Pydimarry 4Phani Pydimarry 4
Perfect Sir.. Thank you somuch
Phani Pydimarry 4Phani Pydimarry 4
I should have paid some attention to detail.. I was always excluding the next month.. Ahh well this is an experience. Thanks
Phani Pydimarry 4Phani Pydimarry 4
Well sorry to bother you again, As I run the SOQL for December, I see there is no data for people moved up in January.. Do you think the above logic has something to do with Year?
Saravana Muthu 8Saravana Muthu 8
I have explained the formula with how the value get calculated.

Please see below.

Assistant supervisor date - 10-10-2017 (10-Oct-2017)
Enrolment Date  - 10-11-2017 (10-Nov-2017)

Formula

AND(YEAR( Assistant_Supervisor_Date__c) = YEAR( Enrollment_Date_Formula__c),MONTH( Assistant_Supervisor_Date__c ) = MONTH( Enrollment_Date_Formula__c )) 
|| AND(YEAR( Assistant_Supervisor_Date__c ) = YEAR( Enrollment_Date_Formula__c ),MONTH(Assistant_Supervisor_Date__c ) = (MONTH(Enrollment_Date_Formula__c)+1),DAY(Assistant_Supervisor_Date__c) <= 10) 

Explanation:

AND(Year(2017=2017),Month(10=10))
||
AND(Year(2017=2017),Month(11=(10+1),10<=10)

Thanks,
Saravana
Phani Pydimarry 4Phani Pydimarry 4
If I have the below dates

Assistant supervisor date - 10-12-2017 (10-Oct-2017)
Enrolment Date  - 10-01-2018 (10-Nov-2017)

AND(Year(2017=2017),Month(12=12))
||
AND(Year(2017=2017),Month(11=(10+1),10<=10) ----- How would this work?
How can I add a Year to this data? 
 
Phani Pydimarry 4Phani Pydimarry 4
Please ignore the SOQL, I am querying in Workbench for the data.. all I am saying is if it is december how can we get this to the previous year?
Phani Pydimarry 4Phani Pydimarry 4
I added this AND(YEAR( Assistant_Supervisor_Date__c ) = (YEAR( Enrollment_Date_Formula__c )+1),MONTH(Assistant_Supervisor_Date__c ) = (MONTH(Enrollment_Date_Formula__c)+1),DAY(Assistant_Supervisor_Date__c) <= 10) tot he Query above
Saravana Muthu 8Saravana Muthu 8
Please use this one.

Data TypeFormula  
AND(YEAR( Movedup_date__c ) = YEAR( Joining_date__c),MONTH(Movedup_date__c ) = MONTH( Joining_date__c)) 

|| 

AND(YEAR( Movedup_date__c) = YEAR( Joining_date__c ),MONTH(Movedup_date__c ) = (MONTH( Joining_date__c)+1),DAY(Movedup_date__c) <= 10) 
|| 
AND(YEAR( Movedup_date__c) = (YEAR( Joining_date__c)+1),MONTH(Movedup_date__c ) = (MONTH( Joining_date__c)-11),DAY(Movedup_date__c) <= 10)

Please let me know if it works.

Thanks,
Saravana
This was selected as the best answer
Saravana Muthu 8Saravana Muthu 8
Sorry I can't able to understand. Can you please explain?

Thanks,
Saravana