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
Denise MeinershagenDenise Meinershagen 

Rolling 12 month formula field

This field should look at the month and year of deadline__c field (date field). Compare it to the current month/year. If it's within the next 12 months, then 'yes', if not then 'no.

For example: today is 12/20/2016. So, December 2016. The deadline on a record is 1/30/2017. So January 2017. This is within the next 12 months, so it should return a value of 'yes'. If the deadline on a record was 1/15/2018, the value would be 'no' because that month/year is more than 12 months from the current month/year.

The formula that I created isn't working as expected because it factors in the date of the month. Just need it to look at month/year. Here is the formula: IF( Deadline__c >= TODAY() + 365, "Yes", "No")
 
Best Answer chosen by Denise Meinershagen
Akhil AnilAkhil Anil
Hi Densie,

You need to create a formula field of the Type TEXT with the below formula. That should work for you.
  
IF(
(
12*(YEAR(deadline__c) - YEAR(TODAY())) + 
(MONTH(deadline__c) - MONTH(TODAY()))
) <= 12,
"Yes",
"No"
)

Kindly mark it as an answer if that works for you !

All Answers

Bryan JamesBryan James
So I dont feel this is an absolute rock star answer but Im giving it a shot any way.
What I did was create a new date using just the year and month off the deadline__c field and then put the day of the month as the 1st of the month: 
DATE(YEAR(deadline__c),MONTH(SkinnyBeeCoding__deadline__c),01)

Then I just compared the 2 using the same thing you had above for todays date: 
>= TODAY() + 365

The only other thing I changed was the True and False return text. Based on your formula you might want the No and Yes switched.
My final output looked like this:
  
IF(DATE(YEAR(Sdeadline__c),MONTH(deadline__c),01) >= TODAY() + 365,"No","Yes")

You could use the same DATE function on todays date in order to keep the day of the month the same: 
>= DATE(YEAR(TODAY() + 365),MONTH(TODAY() + 365),01)

The end outcome looking like: 
IF(DATE(YEAR(deadline__c),MONTH(deadline__c),01) >= DATE(YEAR(TODAY() + 365),MONTH(TODAY() + 365),01),"No","Yes")

I hope this helps.
Have a happy holidays!
Akhil AnilAkhil Anil
Hi Densie,

You need to create a formula field of the Type TEXT with the below formula. That should work for you.
  
IF(
(
12*(YEAR(deadline__c) - YEAR(TODAY())) + 
(MONTH(deadline__c) - MONTH(TODAY()))
) <= 12,
"Yes",
"No"
)

Kindly mark it as an answer if that works for you !
This was selected as the best answer
Denise MeinershagenDenise Meinershagen
So in using this formula, I need to show <=12 but >0. Can you show me how to correct the formula to account for this?

IF(
(
12*(YEAR(deadline__c) - YEAR(TODAY())) +
(MONTH(deadline__c) - MONTH(TODAY()))
) <= 12,
"Yes",
"No"
)