Sign Up ›
  • Balaji Sai Pavan Ayta
  • NEWBIE
  • 10 Points
  • Member since 2015
  • Chatter
    Feed
  • 0
    Best Answers
  • 0
    Likes Received
  • 0
    Likes Given
  • 1
    Questions
  • 5
    Replies
SOLVED
11 replies

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
SOLVED
11 replies

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