function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
SueHallSueHall 

Help with formula - trying to avoid hard coding date

The formula I am using is listed below.  While it works, it is limiting due to the need to hard code the DATE (2009,06,30). 
 
Does anyone know how I can get around harding coding the date? 
If not, how can recode the formula to look at two years, rather than one?  (I think it should be a case function but cannot figure it out)
 
(DATE(2009,06,30) - ( Projected_Start_Date__c )) /365 * (  Annual_Paid_Claims__c )
(DATE(2010,06,30) - ( Projected_Start_Date__c )) /365 * (  Annual_Paid_Claims__c )
Michael SnowMichael Snow
Do you need to make the year dynamic or the month and day as well?

For the year, try
 YEAR(Today()) +1 = 2008
or
 YEAR(Projected_Start_Date__c)
e.g.
(DATE(YEAR(Projected_Start_Date__c) ,06,30) - ( Projected_Start_Date__c )) /365 * (  Annual_Paid_Claims__c )

As for looking at two years, I am not sure what you mean.
SueHallSueHall
Hi Michael,
Thanks for the reply and the suggestions!  The month and day can remain static.  Re: the "two years":
 
The issue is that I have had to hard code our fiscal year into the formula in the past.  This causes an issue when the opportunity (which is where the formula is located) falls in the next fiscal year.  The formula I have been using causes negative numbers in this scenario, due to the hard coded date.  Our fiscal years run from 7/1 through 6/30.  If the "Projected Start Date" falls in the next fiscal year, i.e. after 06/30/2008 I need it to subtract the projected_start_date_c from the end of the 06/30/2009.  This is where I get stuck
 
Detail:
If the projected_start_date_c is between 7/1/07 and 6/30/08 subtract projected_start_date_c from 6/30/2008
 
If the projected_start_date_c is between 7/1/08 and 6/30/09 subtract projected_start_date_c from 6/30/2009
Michael SnowMichael Snow
There are always 184 days remaining in the year after 6/30.  Therefore:
DATE(YEAR(Projected_Start_Date__c+184) ,06,30) - ( Projected_Start_Date__c )) /365 * (  Annual_Paid_Claims__c )

If you shift the date by 184 into the future, then the year of that date is the one you want to work with.
SueHallSueHall

Hi Michael,

This worked perfectly!  Thanks so much for your help!  Have a great weekend.

 

Sue