-3

I've been racking my brain for about 8 hours trying to figure out how this formula works, or I guess WHY it works.

DATE( Year( Contract_Start_Date__c )+floor((MONTH(Contract_Start_Date__c) + Contract_Term__c -1) / 12) , 

mod(MONTH(Contract_Start_Date__c) + Contract_Term__c -1, 12) + 1 , day(Contract_Start_Date__c)) - 1

I understand that it's taking into account 'contract start date' and 'contract terms' to get contract end date. I'm just unsure why FLOOR and MOD are being used. I broke the formula down into a google sheet and was able to figure out that the 'month' argument will produce a year with a decimal place, so using FLOOR to solve to that.

Can anyone break this down further for me?

1

It comes down to "use division to figure out how many years we need to add", and "use the remainder to figure out what month we end up in".

Year( Contract_Start_Date__c )+floor((MONTH(Contract_Start_Date__c) + Contract_Term__c -1) / 12)

This says that if the Contract's start month + the number of months in the term is greater than (strictly greater than) 12, then you need to increment the year.

Let's say the contract starts today, and the term is 12 months.

  • Today's month = 7, term = 12
  • 7 + 12 - 1 = 18
  • FLOOR(19/12) = FLOOR(1.583) = 1

Dividing by 12 and taking the FLOOR ensures that the formula works for contract terms that push the end date out more than one year.
Subtracting 1 from the month + term length ensures that you don't increment the year if the calculated end month is exactly 12

Similarly, mod(MONTH(Contract_Start_Date__c) + Contract_Term__c -1, 12) + 1 tells you what the end month should be.

Using the same example as before

  • 7 + 12 - 1 = 18
  • 18 mod 12 = 6
  • 6 + 1 = 7

A 12 month term contract should end in the same month that it started.
The - 1 in this case prevents contracts that end in December from becoming an invalid date (12 mod 12 = 0, which is not a valid month)

| improve this answer | |
  • Perfect. Thanks for explaining. This will help me immensely. – Nivi Jul 16 '19 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.