Formula giving #ERROR! instead of empty cell

[natalie_stein]

What is wrong with my formula that it is producing "#ERROR!" instead of an empty cell when {Most Recent Completed Event Date} is blank?

DATETIME_FORMAT(IF({Most Recent Completed Event Date}, DATEADD({Most Recent Completed Event Date}, 9, 'month'), ""), 'M/D/YYYY')

It is working perfectly for the ones that have a date in that field (e.g., if "Most Recent Completed Event Date" is 5/13/2023, it gives me 2/13/2024). If "Most Recent Completed Event" is empty, I want the formula to leave the cell empty, hence why I put the empty quotes as the alternative option. But instead, it gives me "#ERROR!"

I tried looking up similar questions but I'm stumped. Thank you!

[kuovonne · Accepted solution]

You are getting the error because the date functions expect a date input but are getting a blank instead. Also, unless there is a specific reason why you want the result to be a text string instead of a date object, you don't need DATETIME_FORMAT().

Try using this simpler formula.

IF(
  {Most Recent Completed Event Date},
  DATEADD({Most Recent Completed Event Date}, 9, 'month')
)

[Alexey_Gusev]

Hi,
Formulas more flexible here, than in Excel.
IF( condition , if_true , if_false ) can be written as
IF ( condition , if_true )  . the default for  third parameter is blank cell.

So if you want to check field for 'non-emptiness' just use IF({Field}, Your_function )
In your example, when field is empty IF(...) returns nothing and DATETIME_FORMAT(...) does not accept nothing as parameter, that's why you have error. To fix, follow solution by @kuovonne 

[natalie_stein]

Worked perfectly, thank you so much!

[natalie_stein]

Thank you for explaining, Alexey! Do the spaces and line breaks impact the IF formula as well?

[Kenneth_Raghuna]

Spaces and line breaks do not impact the formula, they just make it easier for the reader(s) to understand what is happening.