Skip to main content

Getting #ERROR! date field is blank

  • January 16, 2021
  • 1 reply
  • 11 views
_

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

Justin_Barrett
Forum|alt.badge.img+21

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'
    )
)
    Terms & ConditionsAccessibility statement