Skip to main content
Solved

Formula giving #ERROR! instead of empty cell

  • December 9, 2024
  • 5 replies
  • 44 views
natalie_stein
Forum|alt.badge.img+3
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!
 
 

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

 

  

5 replies

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • Answer
  • December 10, 2024

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

 

  

natalie_stein
Alexey_Gusev
Forum|alt.badge.img+25
  • Alexey_Gusev
  • Brainy
  • December 10, 2024

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
natalie_stein
Forum|alt.badge.img+3
  • natalie_stein
  • Author
  • Participating Frequently
  • December 10, 2024

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!

natalie_stein
Forum|alt.badge.img+3
  • natalie_stein
  • Author
  • Participating Frequently
  • December 10, 2024

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?

Kenneth_Raghuna
Forum|alt.badge.img+20

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.

Terms & ConditionsAccessibility statement