Results 1 to 4 of 4

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

Threaded View

LeafyShroom Data Validation: Comparing 2... 02-15-2017, 04:02 PM
6StringJazzer Re: Data Validation:... 02-16-2017, 03:59 PM
LeafyShroom Re: Data Validation:... 02-17-2017, 11:38 AM
6StringJazzer Re: Data Validation:... 02-17-2017, 02:56 PM
  1. #1
    Registered User
    Join Date
    02-15-2017
    Location
    Cape Town, South Africa
    MS-Off Ver
    Microsoft Excel 2016 MSO (16.0.7571.7095) 64-bit
    Posts
    12

    Question 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.
    Last edited by LeafyShroom; 02-15-2017 at 04:13 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 10-28-2015, 12:59 PM
  2. Data validation - intgers bigger than 0 and empty cell not allowed
    By fibonacci1101 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-31-2013, 04:51 PM
  3. Time between timestamps excluding certain hours
    By Silaswulff in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-01-2011, 06:08 PM
  4. Replies: 11
    Last Post: 02-22-2011, 07:07 PM
  5. Comparing a string to values in an array resulting in a Run time Error 1004
    By sgreni in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-15-2009, 04:21 PM
  6. comparing a phone number to an allowed list???
    By juzsp in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-20-2007, 12:00 PM
  7. [SOLVED] how to find time difference between timestamps to a millisecond?
    By sp in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 06-16-2006, 01:15 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1