IF function question for blanks
=IF([Actual Closure Date]@row < [Target Closure Date]@row, "OnTime", "Late")
I have this formula setup where it returns "OnTime" vs "Late". How do I add to the formula to stay blank if one of the column values is blank instead of returning a value. Thank you.
Best Answers

Hey @JayTeeDee
=IF(AND(ISDATE([Actual Closure Date]@row), ISDATE([Target Closure Date]@row)), IF([Actual Closure Date]@row < [Target Closure Date]@row, "OnTime", "Late"),"")
cheers
Kelly

Looks good. Thank you @Kelly Moore
Answers

Hey @JayTeeDee
=IF(AND(ISDATE([Actual Closure Date]@row), ISDATE([Target Closure Date]@row)), IF([Actual Closure Date]@row < [Target Closure Date]@row, "OnTime", "Late"),"")
cheers
Kelly

@Kelly Moore What is the purpose of ISDATE() here? Is it for error checking? Or to make the date calculation correct? What would the formula look like if they were numeric fields instead of date fields?

Hey @ksnyder
The ISDATE looks for date values. The formula reads IF [Actual closure date] is a date and [Target closure date] is a date then proceed with the formula. IF one (or both) are not a date, then blank. Is the formula working for you?
Help me better understand what you mean if they were a numeric field. Are you asking about another column and if that was numeric, or are you asking if the dates aren't really dates but they are text/numbers?

Looks good. Thank you @Kelly Moore

For the same formula, if I want the "On Time" to be displayed for any dates where 'Helper Column' is less than or equal to 'Actual Closure Date', what would I change?
=IF(AND(ISDATE([Actual Closure Date]@row), ISDATE([Helper Column  On Time Closure]@row)), IF([Actual Closure Date]@row < [Helper Column  On Time Closure]@row, "OnTime", "Late"), "")

Hey Jay
So if I understand you correctly, we're swapping the order of the dates and making it less than or equal to vs just less than. If this isn't correctly interpreted, all you have to do is add the equal sign.
To add the equal to portion to either the less than or the greater than, my trick to remembering the order is that the equal sign is always last. Whenever you write these, the equal sign is always last.
=IF(AND(ISDATE([Actual Closure Date]@row), ISDATE([Helper Column  On Time Closure]@row)), IF([Helper Column  On Time Closure]@row<=[Actual Closure Date]@row, "OnTime", "Late"), "")
Does this work for you?
Kelly
Help Article Resources
Categories
Check out the Formula Handbook template!