+ Reply to Thread
Results 1 to 13 of 13

Data Validation won't accept Start Date and End Date from cell values.

  1. #1
    Registered User
    Join Date
    05-03-2010
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2010
    Posts
    14

    Data Validation won't accept Start Date and End Date from cell values.

    Hi Everyone,

    I'd really appreciate some help with a problem I'm having with Data validation in an Excel 2010 workbook.

    I want the date input to be restricted a start date and an End date specified in two cells on the sheet. However, when I set up the Data Validation, ANY date will be accepted.

    As an example cell Z1 contains the date 1/3/2014 and Cell Z2 contains the date 31/3/2014, so in the Data Validation box, the Start Date is referenced to Z1 and the End Date is referenced to cell Z2.

    But I can enter 1/9/2020 and the Data Validation happily accepts that date.

    Thank you in advance for any help anyone can offer.

    Darrell.

  2. #2
    Valued Forum Contributor
    Join Date
    02-09-2006
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 2016
    Posts
    1,075

    Re: Data Validation won't accept Start Date and End Date from cell values.

    It would be easier to help if you can attach a sample of your workbook. Be sure to remove/replace any sensitive data before uploading!
    Trish in Oz
    -------------
    A problem well defined is a puzzle half solved


    If you attach a sample of your workbook it will be easier to find a solution. Remember to remove/replace sensitive data before uploading the file. Look here if you need help with attachments:
    http://www.excelforum.com/faq.php?fa...b3_attachments

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Data Validation won't accept Start Date and End Date from cell values.

    Set the validation like this...

    Allow: Date or Whole number
    Data: Between
    Minimum: =Z1
    Maximum: =Z2

    EDIT: added the Date option
    Last edited by Tony Valko; 03-16-2014 at 08:48 PM.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Data Validation won't accept Start Date and End Date from cell values.

    You need to create a separate list for Start date and End date
    In Data Validation select List and enter a formula something like this = AA2:AA20 (assuming your start date list in this range)
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Registered User
    Join Date
    05-03-2010
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Data Validation won't accept Start Date and End Date from cell values (Worksheet Att).

    Thank you for the responses so far guys, and hopefully I've successfully attached the file (Fictional Data).

    In the 'March' Sheet, Cell AA3 is the Start Date. (The Date is calculated using the current year (Named in the 'INPUT' sheet), and the month from the name of the worksheet.)
    Cell AA6 is the End date.(Calculated from the Start Date)

    You will see from the dates I have entered I can enter any date inside or outside the range defined in the Data Validation.

    Just in case it was the fact that the two dates were the results of formulas, I tried using manually entered dates and still got the same problem.

    Thank you again everyone.

    ROUTE COLLECTIONS Ver1.0.xlsx

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Data Validation won't accept Start Date and End Date from cell values (Worksheet Att).

    Instead of putting the formula in cells on the worksheet you need to put the formulas in those little boxes on the data validation userform.

    Both of the formulas are returning #N/A errors.

    Can you explain what dates the formulas are supposed to return?

  7. #7
    Registered User
    Join Date
    05-03-2010
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Data Validation won't accept Start Date and End Date from cell values.

    Thank you Tony Valko. I have tried your suggestion but it's still not working.

    Thank you Alkey, yes that does work thank you! I will use your kind suggestion, but I still can't help wondering why the much simpler Start Date and End Date validation just doesn't work. Unless I'm doing something wrong (I'm not ruling out the possibility), it would appear that the Data Validation function simply does not work.

    Darrell.

    EDIT: Sorry Tony I must have been typing when you posted your reply. I'll write a further reply to you.
    Last edited by SpecialBrew; 03-16-2014 at 09:59 PM. Reason: Reply came in whilst writing my post.

  8. #8
    Registered User
    Join Date
    05-03-2010
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Data Validation won't accept Start Date and End Date from cell values (Worksheet Att).

    Hi Tony,

    The formulas work fine on my computer (although I do have to press F9 even though automatic calculation is enabled).

    The first formula calculates the first day of the month of the sheet (in this case January) and uses the current year, entered in the 'INPUT' sheet. The second formula calculates the last day of the month of the date in the first calculation. Ergo the first date is 1/3/2014 and the second date is 31/3/2014.

    Like I said, I've just double checked and the formulas both work fine here?

    Thank you again for your help.

    Darrell.

  9. #9
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Data Validation won't accept Start Date and End Date from cell values.

    You're welcome. Don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

  10. #10
    Registered User
    Join Date
    05-03-2010
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Data Validation won't accept Start Date and End Date from cell values.

    Thank you again AlKey. Yes, I'll do both of those things.

    And thank you again Tony Valko, entering those same formulas in the Data Validation dialogue, as you suggested, did the trick very nicely!

    Thank you to everyone for all your help and for solving my problem!

    Darrell.

  11. #11
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Data Validation won't accept Start Date and End Date from cell values (Worksheet Att).

    Hmmm...

    Don't know what happened but when I opened the file again the formulas did return the correct results as you described.

    So, try this...

    Data Validation
    Allow: Custom
    Formula: =AND(INT(B2)=B2,B2>=AA$3,B2<=AA$6)

  12. #12
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Data Validation won't accept Start Date and End Date from cell values.

    Thank you for the feedback!

  13. #13
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Data Validation won't accept Start Date and End Date from cell values.

    Quote Originally Posted by SpecialBrew View Post

    And thank you again Tony Valko, entering those same formulas in the Data Validation dialogue, as you suggested, did the trick very nicely!
    Good deal. Thanks for the feedback!

+ 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] Data Validation to accept values not on the list
    By JO505 in forum Excel General
    Replies: 2
    Last Post: 02-26-2013, 02:44 PM
  2. Replies: 1
    Last Post: 10-12-2012, 06:15 AM
  3. Replies: 1
    Last Post: 10-02-2012, 02:42 PM
  4. Replies: 4
    Last Post: 06-02-2012, 11:26 AM
  5. Data Validation to accept only Date Type
    By pblnrao in forum Excel General
    Replies: 1
    Last Post: 06-13-2011, 09:00 AM

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