function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Balaji Sai Pavan AytaBalaji Sai Pavan Ayta 

Add years and subtract days

Hello, I need help in resolving the following formula.
IF(
AND(Month([CLQ_Program_Transaction__c].CLQ_License_Start_Date__c)=3,Day([CLQ_Program_Transaction__c].CLQ_License_Start_Date__c)=1),
DATE(YEAR([CLQ_Program_Transaction__c].CLQ_License_Start_Date__c) + 1, Month([CLQ_Program_Transaction__c].CLQ_License_Start_Date__c),Day([CLQ_Program_Transaction__c].CLQ_License_Start_Date__c)-2),
DATE(YEAR([CLQ_Program_Transaction__c].CLQ_License_Start_Date__c) + 1, Month([CLQ_Program_Transaction__c].CLQ_License_Start_Date__c),Day([CLQ_Program_Transaction__c].CLQ_License_Start_Date__c)-1)
)
As per the formula, if the month is 3 and day is 1, the formula fails to provide the result. Can someon help me achieve the logic where the result would be 1 day less than the field ([CLQ_Program_Transaction__c].CLQ_License_Start_Date__c) considering leap years as well?

TIA
Best Answer chosen by Balaji Sai Pavan Ayta
Jainam ContractorJainam Contractor
Hi Balaji,

In that case, please check the below formula:

IF(
                AND(
                                MONTH(CloseDate)=2, DAY(CloseDate)=29
                ),
                DATE(YEAR(CloseDate)+1, 2, 28),
                DATE(YEAR(CloseDate)+1, MONTH(CloseDate), DAY(CloseDate))-1
)

This is more simple and efficient formula. Replace CloseDate with your custom field.

Please mark this as the best answer if it helped.

Thanks,
Jainam Contractor

All Answers

Jainam ContractorJainam Contractor
Hi Balaji,

Can you please specift more regarding the requirement. What i understand is : IF Date is 1st March, you want it to update it to the Last day of the February for the Next year...???

Is my understanding correct...???

Thanks,
Jainam.
Balaji Sai Pavan AytaBalaji Sai Pavan Ayta
That's correct! if it is a leap year then it would be feb 29th and for a normal year it would be feb 28th
Jainam ContractorJainam Contractor
Hi Balaji,

Try the below formula which i have created on CloseDate. You can change it to the Custom Field (([CLQ_Program_Transaction__c].CLQ_License_Start_Date__c) you are using:

Formula:
IF( 
                AND(DAY(CloseDate) = 1, MONTH(CloseDate) = 3),
                IF(
                    OR(
                                                AND(MOD(YEAR(CloseDate)+1,4) = 0, MOD(YEAR(CloseDate)+1,100) != 0), 
                                                MOD(YEAR(CloseDate)+1,400) = 0
                                ), 
                                DATE(YEAR(CloseDate)+1, MONTH(CloseDate)-1, 29), DATE(YEAR(CloseDate)+1, MONTH(CloseDate)-1, 28)
                ), NULL
)


Please check and let me know if it works. Please mark this as the solution if it solved your purpose.

Thanks,
Jainam Contractor,
Salesforce Consultant
Varasi LLC
www.varasi.com
Balaji Sai Pavan AytaBalaji Sai Pavan Ayta
Jainam, 
I tweaked your formula a little and seems like it is working for leap years and months with 31 days. Can you review if anything else needs to be changed?
IF( 
AND(DAY(CLQ_License_Start_Date__c) = 1, MONTH(CLQ_License_Start_Date__c) = 3), 
IF( 
OR( 
AND(MOD(YEAR(CLQ_License_Start_Date__c)+1,4) = 0, MOD(YEAR(CLQ_License_Start_Date__c)+1,100) != 0), 
MOD(YEAR(CLQ_License_Start_Date__c)+1,400) = 0 
), 
DATE(YEAR(CLQ_License_Start_Date__c)+1, MONTH(CLQ_License_Start_Date__c)-1, 29), DATE(YEAR(CLQ_License_Start_Date__c)+1, MONTH(CLQ_License_Start_Date__c)-1, 28) 
), IF(OR(AND(MOD(YEAR(CLQ_License_Start_Date__c)+1,4) = 0, MOD(YEAR(CLQ_License_Start_Date__c)+1,100) != 0), 
MOD(YEAR(CLQ_License_Start_Date__c)+1,400) = 0), 
DATE(YEAR(CLQ_License_Start_Date__c)+1, MONTH(CLQ_License_Start_Date__c), DAY(CLQ_License_Start_Date__c)-1), DATE(YEAR(CLQ_License_Start_Date__c)+1, MONTH(CLQ_License_Start_Date__c)-1, DAY(CLQ_License_Start_Date__c)-1)) 
)
Jainam ContractorJainam Contractor
Hi Balaji,

What would you like to do with the months other than March and February...??? 

Please clarify so that i can help you.

Thanks,
Jainam Contractor
Balaji Sai Pavan AytaBalaji Sai Pavan Ayta

The result for my requirement should consider the following criteria:

1. When the [CLQ_License_Start_Date__c] is March 1st of any year, the result should be Feb 28 (for normal year) of next year and Feb 29 (for leap year) of next year. 
2. When the [CLQ_License_Start_Date__c] is March 31st of any year, the result should be March 30 of the next year. This result will also apply for Jan, May, July, August, October and December.
3. When the [CLQ_License_Start_Date__c] is 30th of any month in an year, the result should be 29th of corresponding month in next year
(Considering Year as 2017 then next year becomes 2018)


 

Jainam ContractorJainam Contractor
Hi Balaji,

Does this apply to all the dates...??? Say 1st of any month would be the last day of the Previous Month next year. 

In short, it should be the previous day of the Current day in the next year.

Am i Correct..??

Thanks
Balaji Sai Pavan AytaBalaji Sai Pavan Ayta
That's Correect Jainam! That is my requirement.
Jainam ContractorJainam Contractor
Hi Balaji,

In that case, please check the below formula:

IF(
                AND(
                                MONTH(CloseDate)=2, DAY(CloseDate)=29
                ),
                DATE(YEAR(CloseDate)+1, 2, 28),
                DATE(YEAR(CloseDate)+1, MONTH(CloseDate), DAY(CloseDate))-1
)

This is more simple and efficient formula. Replace CloseDate with your custom field.

Please mark this as the best answer if it helped.

Thanks,
Jainam Contractor
This was selected as the best answer
Balaji Sai Pavan AytaBalaji Sai Pavan Ayta
That's perfect! Thank you so much!
Zac Hodnett 6Zac Hodnett 6

Why not let Salesforce figure it out for you

ADDMONTHS(dateField,12) - 1