Help

Getting #ERROR! date field is blank

Topic Labels: Formulas
1259 1
cancel
Showing results for 
Search instead for 
Did you mean: 
_Barry_Rashawn_
4 - Data Explorer
4 - Data Explorer

I am looking to format the date that is returned by the IF statement but when the date is blank it give me an error. When I just had the if statement without the date formatting it was fine no errors. The formula I used is below! Thanks in advance!

DATETIME_FORMAT(SET_TIMEZONE(IF({MiX STATUS} = “MIXED”, LAST_MODIFIED_TIME(), BLANK()), ‘America/Los_Angeles’), ‘M/D/YYYY’)

1 Reply 1

When there’s no date to format, a formula will throw an error. It’s best to do the test for a date before attempting to format. Instead of formatting BLANK() (i.e. nothing) if the condition portion of the IF() function is false, just skip the format entirely. This just means changing the order of the elements in your formula.

Also keep in mind that the final part of the IF() function is optional. If omitted, the function will automatically return the proper BLANK()-equivalent value based on the rest of the function, so there’s almost never a need to manually insert BLANK().

With all that in mind, here’s a revised version of your formula:

IF(
    {MiX STATUS} = "MIXED",
    DATETIME_FORMAT(
        SET_TIMEZONE(LAST_MODIFIED_TIME(), 'America/Los_Angeles'),
        'M/D/YYYY'
    )
)