+ Reply to Thread
Results 1 to 3 of 3

excel validation rule

  1. #1
    Registered User
    Join Date
    09-06-2015
    Location
    England
    MS-Off Ver
    2010
    Posts
    8

    excel validation rule

    I have this formula in a cell of validation and I need it to let in a time in this format 12:00-18:00 which this current formula does but I need it to let in the word hol to

    =IF(F4="",,(IF(OR(F4="Holi",AND(TIMEVALUE(TEXT((TIMEVALUE(RIGHT(F4,FIND("-",F4)-1))),"hh:mm"))=TIMEVALUE(RIGHT(F4,FIND("-",F4)-1)),TIMEVALUE(TEXT((TIMEVALUE(LEFT(F4,FIND("-",F4)-1))),"hh:mm"))=TIMEVALUE(LEFT(F4,FIND("-",F4)-1)))),TRUE,FALSE)))

    thanks

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,454

    Re: excel validation rule

    Hi,

    Can you simply format the cell hh:mm, then use this to validate the formula?

    =OR(F4="Holi",AND(F4>=0.5,F4<=0.75))
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Registered User
    Join Date
    09-06-2015
    Location
    England
    MS-Off Ver
    2010
    Posts
    8

    Re: excel validation rule

    No as the time in the cell is in the format of 12:00-18:00 and it would not check both times enter in the cell but I have work it out now

    =IF(J6="",,IF(J6="Hol",TRUE,(IF(AND(TIMEVALUE(TEXT((TIMEVALUE(RIGHT(J6,FIND("-",J6)-1))),"hh:mm"))=TIMEVALUE(RIGHT(J6,FIND("-",J6)-1)),TIMEVALUE(TEXT((TIMEVALUE(LEFT(J6,FIND("-",J6)-1))),"hh:mm"))=TIMEVALUE(LEFT(J6,FIND("-",J6)-1))),TRUE,FALSE))))

    thanks of trying to help

+ 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. [SOLVED] Excel Userform with rule on validation fields
    By Megatronixs in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-28-2013, 08:06 AM
  2. Validation rule - 7 letters
    By edhunter in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-11-2013, 10:07 AM
  3. Validation Rule Help
    By dangoodwin1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-31-2012, 04:22 PM
  4. [SOLVED] Excel validation rule help with date ranges
    By Jonah21 in forum Excel General
    Replies: 5
    Last Post: 03-30-2012, 09:15 AM
  5. data validation-setup a validation rule
    By de049 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-07-2008, 08:17 AM
  6. [SOLVED] Validation rule
    By nick in forum Excel General
    Replies: 4
    Last Post: 03-06-2006, 01:00 PM
  7. validation rule
    By andrewm in forum Excel Formulas & Functions
    Replies: 49
    Last Post: 09-06-2005, 12:05 PM
  8. Validation rule but only sometimes
    By Broida (spamless) in forum Excel General
    Replies: 8
    Last Post: 06-26-2005, 06:05 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