Data validation question II

**KHursh** · 05-08-2012, 12:08 PM

Last week I asked about a column in my spreadsheet with two columns with time values (columns AB and AC). The times in column AC cannot be less than the times in column AB. However, sometimes there is no value for column AC and I'd like the user to enter "NA" (nothing else).

The proposed solution was:

=IF( ISTEXT(AC3), OR(AC3="NA"), OR(AB3<AC3) )

This worked great for column AC that had a column AB to compare it to. However, the users now want the values in Column AB to be either a time or "NA" (no other text value). How do I do that?

**ben_hensel** · 05-08-2012, 03:37 PM

If Column AB is NA, what do you want the value to be? If it's got a typo or it's blank, what do you want the value to be then?

I'd wrap another IF that performs a similar check on AB. This assumes you want a text of "NA" in AB3 to deliver TRUE and a typo in AB3 to deliver FALSE and a number to move forward.

Please Login or Register  to view this content.

I also recommend moving from OR() to IF() so you can deliver helpful error messages rather than just "TRUE" or "FALSE". Of course if you're doing any sort of "hunt for false" operation to raise a flag those text messages could gum that up.

Please Login or Register  to view this content.

As written AB3 is text it won't check AC3 at all, because AB3 is checked first; it doesn't really matter what order they're checked in, you could do AC before AB. Checking both for conforming text would require another IF, I think.

Hope this helps.