ShowAll Questionssorted byDate Posted
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!

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.

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!
I'm sorry but I don't understand how to implement it using the parsed dates...
Steve :-/
Actually on second thought I think you might be better off going with a Workflow Rule with a Field Update instead.
wgra

This one should work:

IF(
AND(
CloseDate <= TODAY(),
MONTH(CloseDate) = MONTH(TODAY())
)
,
1,0)

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!

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 :-/
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!
Nope, hence the "Month to Date" =)
wgra

Thanks for adding the YEAR...

Werner