+ Reply to Thread
Results 1 to 7 of 7

Data Validation Formula

  1. #1
    Registered User
    Join Date
    09-12-2018
    Location
    Port of Spain, Trinidad
    MS-Off Ver
    Excel 16
    Posts
    53

    Data Validation Formula

    Hi, I need help with a data validation formula related to time.

    The values involved are:

    Regular Start Time (cell C1)
    Alternative Start Time (cell C2)
    Late Arrival Time (cell C3)


    I want to have a data validation formula within (cell c3) whereby a time is only allowed there if it is late according to 2 simple rules.

    Rule 1) If there is NO Alternative Start Time (ie C2 is blank), then the Late Arrival Time (c3) must be later than the Regular Start Time(c1)
    Rule 2) If there IS an Alternative Start Time(AST) then the Late Arrival Time must be later than the AST irresepctive of the Regular Start Time.

    Logically in my head this should be a fairly easy data validation formula, yet I am stumped at every attempt to solve it.

    See simple screenshot below

    time.PNG

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

    Re: Data Validation Formula

    How about this. If it does not work please attach a sample file for testing.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    09-12-2018
    Location
    Port of Spain, Trinidad
    MS-Off Ver
    Excel 16
    Posts
    53

    Re: Data Validation Formula

    Hey unfortunately that formula didn't work.

    See attached file for testing.
    Attached Images Attached Images

  4. #4
    Registered User
    Join Date
    09-12-2018
    Location
    Port of Spain, Trinidad
    MS-Off Ver
    Excel 16
    Posts
    53

    Re: Data Validation Formula

    Sorry see file attached here.
    Attached Files Attached Files

  5. #5
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

    Re: Data Validation Formula

    In the Data Validation dialog, uncheck the box that says "Ignore Blank"

  6. #6
    Registered User
    Join Date
    09-12-2018
    Location
    Port of Spain, Trinidad
    MS-Off Ver
    Excel 16
    Posts
    53

    Re: Data Validation Formula

    ok solved. thank you very much. jus needed to deselect the ignore blank checkbox.

  7. #7
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

    Re: Data Validation Formula

    You're welcome, and thanks for the rep!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 01-14-2018, 11:04 AM
  2. Replies: 7
    Last Post: 02-12-2016, 01:54 PM
  3. Replies: 4
    Last Post: 07-18-2014, 11:48 AM
  4. [SOLVED] How to use INDEX formula in conjunction with Data Validation to output data.
    By Pope_003 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-09-2014, 11:08 AM
  5. [SOLVED] Formula to display data validation results on another sheet based on data chosen?
    By myoung5149 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-16-2013, 02:53 PM
  6. Need to shorten Data Validation formula in order to fit into formula field
    By V57strat in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-27-2012, 05:31 PM
  7. [SOLVED] Need Data Validation or Formula to Force Entry of Data in .25 Increments
    By sstravs in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-11-2012, 07:16 PM

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