Loading

Date Formula on Report Displays '#Error!' but Displays Correctly at the Record Level

Julkaisupäivä: Jun 10, 2026
Kuvaus

A formula that adds 2 hours to a date/time field using DATETIMEVALUE and TEXT functions will display correctly at the record level but returns #Error! on a report. This is because the TEXT() function applied to a DateTime field appends a trailing 'Z' character (indicating UTC timezone) at the end of the date/time string. For example, TEXT(LastModifiedDate) returns '2016-08-11 17:18:00Z'. The 'Z' suffix is accepted in the record UI but is not a valid format for Salesforce reports.

DATETIMEVALUE ( TEXT ( LastModifiedDate + 2 /24 ) )


In more complex formulas that rebuild a date/time string manually, a space before the seconds value (e.g., '03:30: 00' with a space before '00') causes an invalid format that reports cannot parse.

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
Ratkaisu

 

Fix 1: Remove the Trailing 'Z' from Date/Time Text

To fix the trailing 'Z' issue, wrap the TEXT() expression with a LEFT() function that trims the string to 19 characters, removing the 'Z'. The corrected formula is: DATETIMEVALUE applied to LEFT of TEXT of (LastModifiedDate plus 2 divided by 24) with length 19.

DATETIMEVALUE ( LEFT ( TEXT ( LastModifiedDate + 2 /24 ), 19 ) )

 

Fix 2: Correct Spacing in Manually Constructed Date/Time Strings

Remove the space before the seconds in the time string. The time portion must be written as '03:30:00' (no space before seconds). Also ensure times are in 24-hour format — am/pm notation is not supported in report formulas.

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')

 

Lisäresurssit

DATETIMEVALUE

Knowledge-artikkelin numero

000386123

 
Ladataan
Salesforce Help | Article