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
SkeeterSkeeter 

Checkbox Formula based on Date

Hi,

I'm trying to create a checkbox formula based on if a text field on the record is a previous month and if so set the box to true.
The text field is ImportDate__c (YYYY-MM) and the checkbox is IsPreviousMonth__c

IF
(
MONTH(TODAY()) = 1,
DATE(YEAR(TODAY())-1,12,1) = 
DATE(VALUE(MID(ImportDate__c,0,4)),VALUE(MID(ImportDate__c,5,2)),1),
DATE(YEAR(TODAY()),MONTH(TODAY())-1,1) = 
DATE(VALUE(MID(ImportDate__c,0,4)),VALUE(MID(ImportDate__c,5,2)),1)
)


I'm currently receiving an #Error! in the checkbox on the record.
The record I'm using to test currently has 2013-01 in the ImportDate__c field, so I was expecting the checkbox to be blank.  

Any help is greatly appreciated.

James LoghryJames Loghry
I'm not sure what your formula is trying to do exactly, but here is one reason it may be erroring:

No matter the condition, the checkbox formula always needs to evaluate to either TRUE or FALSE.

For instance, IF(MyDate__c > TODAY(),TRUE,FALSE) is valid.  IF(MyDate__C,MyDate1__c,MyDate2__c) is not valid.

Your formula looks to be evaluating to either a Text or Date criteria, and causing the #Error as a result.

This may not be correct for your requirement, but if you surround your various statements in an AND function instead of an IF function, then you no longer will see the #Error result.  Note, the AND function will return TRUE if all conditions are met, FALSE otherwise.

AND(
MONTH(TODAY()) = 1,
DATE(YEAR(TODAY())-1,12,1) =
DATE(VALUE(MID(ImportDate__c,0,4)),VALUE(MID(ImportDate__c,5,2)),1),
DATE(YEAR(TODAY()),MONTH(TODAY())-1,1) =
DATE(VALUE(MID(ImportDate__c,0,4)),VALUE(MID(ImportDate__c,5,2)),1)
)
Gooch ForeverGooch Forever
Hi

Probably you are using Validation Rule. For which you are getting Error message. If you want your task to be done then use Trigger for 'After Upsert'.
Blessy Voola 4Blessy Voola 4

 lilranger: Try this.

if(value(left(ImportDate__c ,4))=year(today()),if(month(today())-value(right(ImportDate__c ,2))=1,true,false),if(and(year(today())-value(left(ImportDate__c ,4))=1,month(today())-value(right(ImportDate__c ,2))=-11),true,false))

Thanks!