IF function question for blanks

JayTeeDee

=IF([Actual Closure Date]@row < [Target Closure Date]@row, "On-Time", "Late")

I have this formula setup where it returns "On-Time" 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.

Kelly Moore

Hey @JayTeeDee

=IF(AND(ISDATE([Actual Closure Date]@row), ISDATE([Target Closure Date]@row)), IF([Actual Closure Date]@row < [Target Closure Date]@row, "On-Time", "Late"),"")

cheers

Kelly

JayTeeDee

Looks good. Thank you @Kelly Moore

Kelly Moore

Hey @JayTeeDee

=IF(AND(ISDATE([Actual Closure Date]@row), ISDATE([Target Closure Date]@row)), IF([Actual Closure Date]@row < [Target Closure Date]@row, "On-Time", "Late"),"")

cheers

Kelly

ksnyder

@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?

Kelly Moore

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?

JayTeeDee

Looks good. Thank you @Kelly Moore

JayTeeDee

@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, "On-Time", "Late"), "")

Kelly Moore

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, "On-Time", "Late"), "")

Does this work for you?

Kelly