+ Reply to Thread
Results 1 to 10 of 10

Assistance with data validation for dates

  1. #1
    Registered User
    Join Date
    12-18-2015
    Location
    US
    MS-Off Ver
    2010
    Posts
    8

    Question Assistance with data validation for dates

    I have a spreadsheet that will contain 3 dates. The first date that can be entered will be a date of birth, a few columns later there will be a date sample was taken followed by date the sample was analyzed. I would like to ensure that the DOB meets certain criteria. DOB must not make someone older than 90 years old, DOB should not be for someone who is less than 60 days old (no newborns). For the date sample was taken, oftentimes the end user will accidentally put in the dob instead of the date of the test/sample, so I want it to always be greater than (ideally 60 days more than) the birth date, but not a future date. For the date analyzed, it cannot be before the date the sample was taken, cannot be before the person was born, cannot be in the future, and I would like it to flag (but allow the entry) if the date analyzed is greater than 14 days from the date the sample was taken?

    examples: Assuming Today is 01/20/2017, then DOB cannot be after 11/20/2016 (or whatever 60 days is), the date of the sample can be today's date (1/20/2017, and the date analyzed can also be today's date (date analyzed may equal date of sample, but date analyzed cannot be before date sampled) If date sampled = 12/20/2016, and user enters 01/20/2017 for date analyzed, there should be an alert that would ask if they were sure they waited 30 days (doesn't have to specify the difference between the dates, it can just say 'did you really not analyze this within 2 weeks?) to analyze a sample? (but, if they really did screw up like this, then it allows the date to be entered). What would the data validation formulas look like for these instances??

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Assistance with data validation for dates

    for the DOB, use this rule....
    =AND(E2 < TODAY()-60,E2 > EDATE(TODAY(),-1080))
    Adjust E2 as needed

    For the Taken...
    =F2 < TODAY()-60

    For the Analysed...
    =AND(G2 > E2,G2 > F2,G2 < =TODAY())
    For the flag, use Conditional Formatting with this Rule...
    =G2 > F2+14

    E
    F
    G
    1
    DOB Taken Checked
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    12-18-2015
    Location
    US
    MS-Off Ver
    2010
    Posts
    8

    Re: Assistance with data validation for dates

    I am not sure how to put those in where I want them?
    The problem is that this will be distributed to people who will fill it out and then it will be dropped into a place
    where it will be automatically parsed into a pseudo HL7 message, so I won't be able to really look at it and fix the problems
    if users do not fill it out correctly.
    I am trying to prevent as many chances of people putting in the wrong information as possible.
    Unfortunately there will be 100's of users with different levels of experience in excel.
    I was trying to add data validation to each of the red columns (red columns must have information in them).
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Assistance with data validation for dates

    Thanks for the file, but I got an error message when I tried to open it. maybe try uploading again?

  5. #5
    Registered User
    Join Date
    12-18-2015
    Location
    US
    MS-Off Ver
    2010
    Posts
    8

    Re: Assistance with data validation for dates

    Let me know if this one works.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Assistance with data validation for dates

    Try the following. For D2:D1000, in the Data Validation Start Date paste: =EDATE(TODAY(),-12*90)
    Amend the Error Alert to read: Incorrect date. Patient should be atleast 60 days old and not over 90 years of age.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Registered User
    Join Date
    12-18-2015
    Location
    US
    MS-Off Ver
    2010
    Posts
    8

    Re: Assistance with data validation for dates

    Is there a way to enter the date without the / (have them added automatically) whilst still keeping the data validation?

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Assistance with data validation for dates

    I am not going to say that there isn't, however I don't think that it works the way you want. For example select a cell in column D, change the format of the cell from 'General' (as it is now) to 'Short Date'. Type in 12016 and press enter. The cell will indeed display a date with the /'s however that date will be 11/23/1932 as that is the 12016th day after 1/1/1900, which is how Excel actually stores dates. To get the cell to display 1/20/2016 you'd need to type in 42389 as Jan 20, 2016 is 42,389 days past 1/1/1900. Keeping track of the days since 1/1/1900 seems to me much harder than typing in /'s
    Now it is very possible that someone knows a way to do what you want, so lets see if anyone else replies and we both learn something new.

  9. #9
    Registered User
    Join Date
    12-18-2015
    Location
    US
    MS-Off Ver
    2010
    Posts
    8

    Re: Assistance with data validation for dates

    Thanks, maybe someone will know of a way, otherwise, yes, I don't want to try to keep up with those numbers.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Assistance with data validation for dates

    You could probably enter dates like you want, but then you would need additional formulas elsewhere - or VBA code - to be able to work with them. It is almost always better to just use real dates, rather than try and short-circuit the system

+ 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. Data validation and dates
    By jtheule in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-13-2016, 04:08 PM
  2. Advanced Validation Assistance Needed
    By buxfan in forum Excel General
    Replies: 3
    Last Post: 02-10-2012, 04:08 PM
  3. Assistance with a 'Conditional' Validation List
    By AppSupportKarl in forum Excel General
    Replies: 1
    Last Post: 08-02-2011, 09:46 AM
  4. Data validation with dates
    By kadams99 in forum Excel General
    Replies: 1
    Last Post: 12-20-2010, 09:37 PM
  5. Data Validation Assistance Please
    By ABabeNChrist in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-12-2010, 12:35 PM
  6. Mouse scroll WITHIN a data validation list & option macro assistance
    By HOT97ECLIPSEGSX in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 01-11-2010, 11:11 PM
  7. [SOLVED] Dates using Data / Validation
    By susan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-12-2006, 07:30 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