0

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))))))))))))

1 Answer 1

1

You need to account for the fact that months wrap around from 12 to 1 every year. This means that, four months from now, it will be month 2, not month 14. You can also use a CASE statement to make things simple.

IF(YEAR(Schedule_Date__c)-YEAR(Closed_Date__c)<2,
CASE(
  MOD(
    YEAR(Schedule_Date__c)*12+(MONTH(Schedule_Date__c)-1)-
    YEAR(Closed_Date__c)*12+(MONTH(Closed_Date__c)-1),
    12),
  0, Onboarding_Amount__c,
  1, Onboarding_Amount__c*2,
  2, Onboarding_Amount__c*3,
  3, Onboarding_Amount__c*4,
  4, Onboarding_Amount__c*5,
  Schedule_Amount__c),
  NULL)
6
  • Thanks. I guess I need to play around with this a bit. As is ...despite the field names being correct ...I'm not getting expected results. Oct 10, 2018 at 16:37
  • Just one question. Not sure why there is this being multiplied by 12. [YEAR(Schedule_Date__c)*12.]. Looks like its being multiplied by 12 years? Oct 10, 2018 at 17:19
  • @ScottJanis We convert years to months so we can subtract them. For example, Jan 2019 would be 24,228, while Oct 2018 would be 24,225, which when subtracted shows that there are three months difference. Does that help?
    – sfdcfox
    Oct 10, 2018 at 17:22
  • Yes, The odd thing is that the onboarding amount field, the ones that's supposed to render for months 1->5 is not showing up at the first amount. The schedule amount shows up and THEN somewhere ..three or four month in..it looks like the process then starts to cycle in. Wish I could upload an attachment to show you. Oct 10, 2018 at 17:43
  • @ScottJanis You're welcome! My formula presumes that Schedule_Date__c will be greater than or equal to Closed_Date__c. You may need to account for that.
    – sfdcfox
    Oct 10, 2018 at 18:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .