Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
go_jobing!go_jobing! 

Month to Date Formula Using Opportunity Close Date

IF( CloseDate <= TODAY() , 1,0) almost gets me where I want to go. 

 

I'm trying to create a true or false (1 or 0) result to help me determine revenue this month to date.  How do I limit the above results to THIS_MONTH in a formula field?

 

Best Answer chosen by Admin (Salesforce Developers) 
go_jobing!go_jobing!
IF(
AND(
CloseDate <= TODAY(),
MONTH(CloseDate) = MONTH(TODAY()),
YEAR(CloseDate) = YEAR(TODAY())), 1,0)
 
Thanks so much, you are both right - I'm pretty sure the above is working properly.


All Answers
Steve :-/Steve :-/
You probably have to parse out the Month and Year of you date field using 
 
MONTH(CloseDate)
YEAR(Close Date)
 
MONTH(Today()) 
YEAR(Today())  


go_jobing!go_jobing!
I'm sorry but I don't understand how to implement it using the parsed dates...


Steve :-/Steve :-/
Actually on second thought I think you might be better off going with a Workflow Rule with a Field Update instead.


wgrawgra
This one should work:
 
IF( 
AND( 
CloseDate <= TODAY(), 
MONTH(CloseDate) = MONTH(TODAY()) 

) 
, 
1,0) 


Steve :-/Steve :-/
This is the sort of formula I was talking about (don't bother with a Workflow Rule, it was late and I wasn't thinking right).  
 
You have to also evaluate the CloseDate YEAR and Current YEAR otherwise Opportunities that closed in the same month but in a previous year will be incorrectly flagged. 
 
IF( 
AND( 
MONTH(CloseDate) = MONTH(TODAY()), 
YEAR(CloseDate) = YEAR(TODAY())), 1,0)
 
 
 
Message Edited by Stevemo on  01-28-2010 10:17 AM


go_jobing!go_jobing!
IF(
AND(
CloseDate <= TODAY(),
MONTH(CloseDate) = MONTH(TODAY()),
YEAR(CloseDate) = YEAR(TODAY())), 1,0)
 
Thanks so much, you are both right - I'm pretty sure the above is working properly.
This was selected as the best answer
Steve :-/Steve :-/
No problem, I thought you just wanted to return a "1" if the Close Date = Current Month.  I didn't know that you wanted to exclude opportunities due to close between now and the end of the current month.   


go_jobing!go_jobing!
Nope, hence the "Month to Date" =)


wgrawgra
Thanks for adding the YEAR... 
Werner