Date formula on report shows "#Error!" while working on the record in the user interface?
|Knowledge Article Number||000181745|
|Description||A simple formula like:
DATETIMEVALUE ( TEXT ( LastModifiedDate + 2 /24 ) )Will show fine in the normal user interface but shows: "#Error!" on a report.
A more complex scenario might be:
DATETIMEVALUE( TEXT( YEAR( DATEVALUE( *FIELD* ))) + '-' + TEXT( MONTH( DATEVALUE( *FIELD* ))) + '-' + TEXT( DAY( DATEVALUE( *FIELD* ))) + ' 3:30: 00')
DATETIMEVALUE for the user interface is more easy going on the data given, while a report would expect the exact parameters.
When you use the following formula:
TEXT( LastModifiedDate )The result will look like:
2016-08-11 17:18:00ZConverting this back, a report will not know how to interpret the Z at the end, a report is expecting:
The first formula can be corrected, to show fine on a report in the following way:
DATETIMEVALUE ( LEFT ( TEXT ( LastModifiedDate + 2 /24 ), 19 ) )
For the second example, the report is not accepting the format, as the space between the minutes and the seconds creates an unaccepted type of date/time.
You need to keep the space before the hour, but remove the one before the seconds, this way:
Incorrect: ....+ Text(DAY(DATEVALUE(*FIELD*)))+' 03:30: 00') Correct: ....+ Text(DAY(DATEVALUE(*FIELD*)))+' 03:30:00')Also make sure to use 24 hour times, am/pm will not be understood on reports either.