ShowAll Questionssorted byDate Posted
SFDC AVINASH

# Formula Correction!

Hi All,
I am trying to impliment the bellow using forumla.
If(TODAY() > (StartDate + (Today -StartDate), 1, 0)
Ex: Today:21/Feb/2015
Start Date:21/Jan/2014
Formula: 21/Feb/2015 > (21/Jan/2014 + ( 1 ) )

Note: In above (Today -StartDate) will give me the Number of Years difference between the two.

How can i write a forumla for this senario?
Best Answer chosen by SFDC AVINASH
BalajiRanganathan
Try the below sample instead of using YEAR function

CEILING((TODAY() - Startdate__c + 1)/365)

I think this might be your complete formula.

IF( Enddate__c < TODAY(),  CEILING((Enddate__c - Startdate__c + 1)/365), IF( Startdate__c <= TODAY(), CEILING((TODAY() - Startdate__c + 1)/365), 1))

BalajiRanganathan
You have to use YEAR function to get the year.
IF(TODAY() > (StartDate + YEAR(TODAY()) - YEAR(StartDate)), true, false)
SFDC AVINASH
Thank you for your reply. I am using YEAR in my actual formula, i missed to mention it here. But the problem i am facing is as bellow. IF( TODAY() >= (StartDate__c + (YEAR(TODAY()) - YEAR(StartDate__c))), IF( DATEVALUE(EndDate__c) >= TODAY(), (YEAR(TODAY()) - YEAR(StartDate__c)) + 1, (YEAR(DATEVALUE(EndDate__c)) - YEAR(StartDate__c))), IF( DATEVALUE(EndDate__c) >= TODAY(), YEAR(TODAY()) - YEAR(StartDate__c), (YEAR(DATEVALUE(EndDate__c)) - YEARStartDate__c))) ) I will be calculating the grade in which the student is studying based on the Start day of the school education and last day of completion of his school education. But if the Start Day is Jun/1/2014 and End Date is Jun/1/2020 then i expect my formula shod be get the number "1" since its not even 1 year he completed in his school. Whereas if the StartDate is Jan/20/2014 and end date is same as above then my fomula should get the value 2. The formula i wrote above is not considering the Date, its considering only YEAR value. Please help me on this.
BalajiRanganathan
Try the below sample instead of using YEAR function

CEILING((TODAY() - Startdate__c + 1)/365)

I think this might be your complete formula.

IF( Enddate__c < TODAY(),  CEILING((Enddate__c - Startdate__c + 1)/365), IF( Startdate__c <= TODAY(), CEILING((TODAY() - Startdate__c + 1)/365), 1))
This was selected as the best answer