Print this page

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:00Z
Converting this back, a report will not know how to interpret the Z at the end, a report is expecting:
2016-08-11 17:18:00
Resolution
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.
 




promote demote