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
Alex.AcostaAlex.Acosta 

Formula works on page layout, but displays #Error! on Report

I'm having an issue with a age range formula that's returning a numeric value back. On the page layout it displays the field value correctly, but whenever I try to run a report with it, it just throws me the error on the field column value. Anyone has this issue before?

 

Formula:

IF( 
	AND(
		NOT(ISNULL(CustomFieldDate__c)),
		DateValue(TEXT(CustomFieldDate__c)) > DateValue(Text(CreatedDate))
	),
	DateValue(TEXT(CustomFieldDate__c)) - DateValue(Text(CreatedDate)), 
	0
)

 

Best Answer chosen by Admin (Salesforce Developers) 
goabhigogoabhigo

Hi Alex,

 

Just tried in my Dev org. It showed #Error! in my report too. So digged more about it - couldn't find a supporting document. So went back to what I am good at - trouble shooting. I just changed:

 

IF( 
	AND(
		NOT(ISNULL(CustomFieldDate__c)),
		CustomFieldDate__c > DateValue(CreatedDate)
	),
	CustomFieldDate__c - DateValue(CreatedDate), 
	0
)

Please note that CustomFieldDate__c in my Org is Date field, not DateTime. If your's is DateTime, then you have to use DateValue(CustomFieldDate__c).

 

My guess is that it is showing error because your converting date to text, again creating DateValue from text, report is not able to compile it. Just guessing...

All Answers

goabhigogoabhigo

Hi Alex,

 

Just tried in my Dev org. It showed #Error! in my report too. So digged more about it - couldn't find a supporting document. So went back to what I am good at - trouble shooting. I just changed:

 

IF( 
	AND(
		NOT(ISNULL(CustomFieldDate__c)),
		CustomFieldDate__c > DateValue(CreatedDate)
	),
	CustomFieldDate__c - DateValue(CreatedDate), 
	0
)

Please note that CustomFieldDate__c in my Org is Date field, not DateTime. If your's is DateTime, then you have to use DateValue(CustomFieldDate__c).

 

My guess is that it is showing error because your converting date to text, again creating DateValue from text, report is not able to compile it. Just guessing...

This was selected as the best answer
Alex.AcostaAlex.Acosta

That seemed to be the problem. Thank you very much for your help!