Skip to main content
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!
 
 

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

 

  

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 

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

 

  


Worked perfectly, thank you so much!

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 


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

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


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

Reply


Terms & Conditions