You need to sign in to do that
Don't have an account?
Scott Janis 6
Formula field acting on a revenue schedule , but will not recognize schedules which in include part of one year and part of another
Hello,
I have a custom object that is essentially pulling in a calculated amount based on the schedule date , and then after 5 months, just pulls in the schedule amount. This works like a charm if the entire 12 month rev schedule is within 2018 or 2019 exlcusively. However if the scheduler is for example three months in 2018 (Oct , Nov and Dec) and the the remaining schedule goes into 2019, I get no results for those remaining 12 months. I've tried multiple variations of the statement below. I should also poing out that I have a trigger which moves the schedule based on the close date.
If ( MONTH(Closed_Date__c)=MONTH( Schedule_Date__c ), Onboarding_Amount__c ,
If ( (MONTH(Closed_Date__c)+1)=MONTH(Schedule_Date__c) ,Onboarding_Amount__c *2,
If ( (MONTH(Closed_Date__c)+2)=MONTH(Schedule_Date__c) , Onboarding_Amount__c *3 ,
If ( (MONTH(Closed_Date__c) +3) =MONTH(Schedule_Date__c), Onboarding_Amount__c *4 ,
If ( (MONTH(Closed_Date__c) +4) =MONTH(Schedule_Date__c), Onboarding_Amount__c *5,
If ( (MONTH(Closed_Date__c) +5) =MONTH(Schedule_Date__c), Schedule_Amount__c ,
If ( (MONTH(Closed_Date__c) +6) =MONTH(Schedule_Date__c), Schedule_Amount__c ,
If ( (MONTH(Closed_Date__c) +7) =MONTH(Schedule_Date__c), Schedule_Amount__c,
If ( (MONTH(Closed_Date__c) +8) =MONTH(Schedule_Date__c), Schedule_Amount__c,
If ( (MONTH(Closed_Date__c) +9) =MONTH(Schedule_Date__c), Schedule_Amount__c,
If ( (MONTH(Closed_Date__c) +10) =MONTH(Schedule_Date__c), Schedule_Amount__c ,
If ( (MONTH(Closed_Date__c) +11) =MONTH(Schedule_Date__c), Schedule_Amount__c ,
NULL))))))))))))
I have a custom object that is essentially pulling in a calculated amount based on the schedule date , and then after 5 months, just pulls in the schedule amount. This works like a charm if the entire 12 month rev schedule is within 2018 or 2019 exlcusively. However if the scheduler is for example three months in 2018 (Oct , Nov and Dec) and the the remaining schedule goes into 2019, I get no results for those remaining 12 months. I've tried multiple variations of the statement below. I should also poing out that I have a trigger which moves the schedule based on the close date.
If ( MONTH(Closed_Date__c)=MONTH( Schedule_Date__c ), Onboarding_Amount__c ,
If ( (MONTH(Closed_Date__c)+1)=MONTH(Schedule_Date__c) ,Onboarding_Amount__c *2,
If ( (MONTH(Closed_Date__c)+2)=MONTH(Schedule_Date__c) , Onboarding_Amount__c *3 ,
If ( (MONTH(Closed_Date__c) +3) =MONTH(Schedule_Date__c), Onboarding_Amount__c *4 ,
If ( (MONTH(Closed_Date__c) +4) =MONTH(Schedule_Date__c), Onboarding_Amount__c *5,
If ( (MONTH(Closed_Date__c) +5) =MONTH(Schedule_Date__c), Schedule_Amount__c ,
If ( (MONTH(Closed_Date__c) +6) =MONTH(Schedule_Date__c), Schedule_Amount__c ,
If ( (MONTH(Closed_Date__c) +7) =MONTH(Schedule_Date__c), Schedule_Amount__c,
If ( (MONTH(Closed_Date__c) +8) =MONTH(Schedule_Date__c), Schedule_Amount__c,
If ( (MONTH(Closed_Date__c) +9) =MONTH(Schedule_Date__c), Schedule_Amount__c,
If ( (MONTH(Closed_Date__c) +10) =MONTH(Schedule_Date__c), Schedule_Amount__c ,
If ( (MONTH(Closed_Date__c) +11) =MONTH(Schedule_Date__c), Schedule_Amount__c ,
NULL))))))))))))