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
Amanda JonesAmanda Jones 

Need formula to filter current year and month in custom field formula

I'm writing a formula field and need a little help to get it just right.

Here is the formula:

IF(( CloseDate )<=DATE(YEAR((CloseDate)),Month((CloseDate)),15),15,0)

The problem is that the formula is returning records for every year and I only want it return records for the current year. I have tried using the Today() function but I am not putting it in the right place.

Please help!

Thanks in advance.


 
Best Answer chosen by Amanda Jones
Amanda JonesAmanda Jones
Figured it out. Here is what worked: 
IF(YEAR(CloseDate) = YEAR(TODAY())&&DAY(CloseDate)<16&& Month(CloseDate)+1=Month(Today())
,15,0)

All Answers

Steven NsubugaSteven Nsubuga
To get records that were closed this year, write something like this in your formula 
IF(YEAR(CloseDate) == YEAR(TODAY()), true, false)
That should allow you to return true for all records that were closed this year.
 
Amanda JonesAmanda Jones
Thank you Steven.

I am not just looking for the year though.

I need the formula to return all the records that have a CloseDate on or before the 15th of the month for the current year only.

Right now my formula returns all records that have a CloseDate on or before the 15th for every year for every year.

Is there a way to add the Today() function into this formula:

IF(( CloseDate )<=DATE(YEAR((CloseDate)),Month((CloseDate)),15),15,0)
Steven NsubugaSteven Nsubuga
Hi Amanda, try this 
IF(( CloseDate )<=DATE(YEAR((TODAY())),Month((TODAY())),15),15,0)

 
Amanda JonesAmanda Jones
Figured it out. Here is what worked: 
IF(YEAR(CloseDate) = YEAR(TODAY())&&DAY(CloseDate)<16&& Month(CloseDate)+1=Month(Today())
,15,0)
This was selected as the best answer