Data Validation: Comparing 2 timestamps - If time, then may not =, if string, then allowed

Hi There,

I'm having some trouble solving the following Data Validation Problem:

I have two columns H and I. Column H cells contain start-times [HH:MM:SS] and cells in column I contain end-times [HH:MM:SS].

However, cells in these two columns can also contain specific strings like "NA" / "Skipped". Only if the start- and end-time values are time values, the may not be equal to each other. However, if the cells in columns H or I contain any of the String values, they may equal each other. Otherwise it must show an error.

I have come this far:
=AND(OR(TEXT($I3,"hh:mm:ss")="NA",TEXT($I3,"hh:mm:ss")="Aborted",TEXT($I3,"hh:mm:ss")="Skipped",NOT(ISERROR(TIMEVALUE(TEXT($I3,"hh:mm:ss"))))),NOT(TEXT($H3,"hh:mm:ss")=TEXT($I3,"hh:mm:ss")))

The above formula works, but it does not allow the H+I cells to be the same in case they are string values... It stops any values compared in columns H+I from being the same, no matter what.

I will greatly appreciate any suggestions!

Thanks.

I get this:

Formula:

=OR(AND(OR($I3="NA",$I3="Aborted",$I3="Skipped"),$H3=$I3),AND(NOT(ISERROR(TIMEVALUE(TEXT($I3,"hh:mm:ss")))),NOT(ISERROR(TIMEVALUE(TEXT($H3,"hh:mm:ss")))),$H3<>$I3))

I interperted your rule as:

Result is TRUE if:
1. Both values are valid times and are different*
2. Both values are valid text strings and the same

Result is FALSE in all other cases

*You didn't say so but you should probably ensure that end time is later than start time.

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking on addreputationicon.jpg below their name.