Sign Up ›

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

Need help with date field

I have a field on Opportunity that calculates number of forecast days 30,60,90 based on todays date. The formula is as below

If( Year(CloseDate) = Year(Today())+1,

IF(MONTH(CloseDate) = MONTH(Today()) , '30 Day',

IF(MONTH(CloseDate) = MONTH(Today()) + 1, '60 Day',

IF(MONTH(CloseDate) = MONTH(Today()) + 2, '90 Day','No'))), 

'No')

 

This formula doesnt work if close data is 02/01/2013 .The answer should be '90 Day' but it is 'No'.Can anyone help with the formula.

Best Answer chosen by Admin (Salesforce Developers) 
sivaextsivaext

hi 

 

try this 

 

If( Year(CloseDate) = Year(Today())+1,
if((MONTH(Today())=11 && MONTH(CloseDate) =2),'90 Day',
if((MONTH(Today())=12 && MONTH(CloseDate) =1), '60 Day', if((MONTH(Today())=12 && MONTH(CloseDate) =2) , '90 Day',
IF(MONTH(CloseDate) =MONTH(TODAY()) , '30 Day',
IF(MONTH(CloseDate) = MONTH(Today())+1, '60 Day',
IF(MONTH(CloseDate) = MONTH(Today()) + 2, '90 Day','No')))))),
'No')

All Answers

sivaextsivaext

hi 

 

try this 

 

If( Year(CloseDate) = Year(Today())+1,
if((MONTH(Today())=11 && MONTH(CloseDate) =2),'90 Day',
if((MONTH(Today())=12 && MONTH(CloseDate) =1), '60 Day', if((MONTH(Today())=12 && MONTH(CloseDate) =2) , '90 Day',
IF(MONTH(CloseDate) =MONTH(TODAY()) , '30 Day',
IF(MONTH(CloseDate) = MONTH(Today())+1, '60 Day',
IF(MONTH(CloseDate) = MONTH(Today()) + 2, '90 Day','No')))))),
'No')

This was selected as the best answer
goabhigogoabhigo

I do not understand what you are trying to do exactly.

 

In the first check you are checking whether CloseDate is next year, if yes then you are checking CloseDate is same as this Month (next year, this month), if yes then '30 Day' !!

 

Is that what you are looking into? Then, your CloseDate = 12/14/2013, it works - it shows 30 Day.

 

--OR--

 

You can calculate number of days difference between Today() and CloseDate() [ just use CloseDate - Today() ]. Now depending on the difference, say <=30 return '30 Day', and so on...
SAPOCSAPOC

Thank you