• Home
  • Ideas
  • LIfecycle in Weeks formula - please validate and prep for either patch soon or May release worst cas

LIfecycle in Weeks formula - please validate and prep for either patch soon or May release worst cas

Related products: None
We've been holding on updating the managed 'Lifecycle in Weeks' formula field on our Customer Info object until we have a validated formula - we've got a few customers waiting for this field to function as originally intended:  provide a week number, 1-52, based on the anniversary of the Original Contract Date.





I've provided a formula below. I've also set this formula field up in a demo org and tested it - I can pass along the credentials if you want to look.





Basically, the formula works as follows:


1. builds a date in the current year that is the same month and day as the Orig. Contract Date 


2. Subtracts this date from today's date.


3. If the result is positive, then it divides the different by 7 and rounds to get a week number.


4. If negative (meaning that the Orig Date day and month is later in the year than today), it creates a new date for LAST year with the same day and month as the Orig. Contract Date, THEN subtracts and divides by 7.





What's the process for pushing this along to resolution?  In a perfect world, I'd like to update this as a patch before the May release - Blackline in particular has been pushing for this, although I gave them the formula to drive off a field they create while they wait.  





I definitely want Prod Eng to validate the formula independently - let me know if you have any questions.





FORMULA:





IF ( 


TODAY() - DATE (YEAR( TODAY () ),MONTH ( JBCXM__OriginalContractDate__c ) , DAY ( JBCXM__OriginalContractDate__c ))>=0, 


FLOOR((TODAY() - DATE (YEAR( TODAY () ),MONTH ( JBCXM__OriginalContractDate__c ) , DAY ( JBCXM__OriginalContractDate__c )))/7)+1, 


FLOOR((TODAY() - DATE (YEAR( TODAY ()-365),MONTH ( JBCXM__OriginalContractDate__c ) , DAY ( JBCXM__OriginalContractDate__c )))/7)+1)





PS.  This started as a chatter feed in GonG.  Snip att'd.











We will evaluate this formula at our end. One problem i see with replacement of existing formula field is that same field is used by Black line (https://jbarasoftware.jira.com/browse/JB-9668) and they are using the formula which we fixed earlier. I would suggest to have another field with different label so that it works with for both the use case. One more suggestion let's continue the conversation on chatter instead of spreading into multiple places.
Scott - will this work for contracts closed on "leap day"?  I couldn't resist.  LOL
Embarrassingly enough, no - just realized that today and haven't written it into the formula yet. Sigh. Hoping to post a correction later this evening. BUT THANKS FOR POINTING OUT MY FLAWS ELAINE!  :)
You have no flaws, Scott!!  Even a blind squirrel gets a nut every once and a while...  (I am the blind squirrel in this case and leap day is the nut).  
Need help how to crack other nut i.e Come up with different name & label for this formula as we cannot change the existing field so from the engineering perspective, we have to add new fields and can only be included in major release.
Scott any inputs on leap year support and any input on label,field as we need to support both the use case.
We need to get this figured out ASAP.  Every class of Express Workshop includes this content and we don't have a mechanism to correctly enable this behavior so we are having customers configure the rules using the current field knowing that it won't work for Accounts older than 1 year.
Terms & ConditionsCookie settings