2

I have a requirement where I need to populate records where a criteria is true. The criteria is the users must have joined in between Jul,2018 to May,2019. I thought I can achieve this using a formula field. Below is the formula:

AND((Join_Date__c >= DATE(2018,07,01)),(Join_Date__c <= DATE(2019,04,30)))

this would return true if both the conditions satisy. But when I see in the record, it is displayed as "#Error!" instead of true or false

I do not understand where I have done a mistake. Can anyone please help me with this.

4
  • Do you want to display true in the formula field if joining date falls in your criteria, and false otherwise. Is it?
    – sfdc_sk
    Mar 1 '19 at 8:19
  • Yes @sfdc_sk...True and false in the sense, check or uncheck
    – S kanth
    Mar 1 '19 at 8:21
  • What datatype are you using for this formula field? Mar 1 '19 at 8:27
  • @rahulgawale return data type is checkbox
    – S kanth
    Mar 1 '19 at 8:30
5

Use this formula.

IF( AND( Join_Date__c >= DATE(2018,07,01), Join_Date__c <= DATE(2019,04,30) ), true, false )

The mistake in your formula is April doesnt have 31 days

DATE(2019,04,31) is not correct
5
  • Thanks for your concern over this issue....I tried using your formula. It also returned error as value in the field
    – S kanth
    Mar 1 '19 at 9:01
  • Then probably you will have to check FLS for the field named Join_Date__c Mar 1 '19 at 9:06
  • It is set to visible for all the profiles and being a system admin, atleast, I would have access to all fields. Even in the field accessibility, it is set to editable for system admin profile
    – S kanth
    Mar 1 '19 at 9:09
  • I have modified the formula in the answer. Try that! Mar 1 '19 at 9:26
  • 1
    In your formula, you are using the 31st day of April in the second condition. However April has only 30 days. That was the problem. Mar 1 '19 at 9:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.