# Assistance with data validation for dates

1. ## 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. ## Re: Assistance with data validation for dates

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

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

3. ## 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).

4. ## 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. ## Re: Assistance with data validation for dates

Let me know if this one works.

6. ## 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.

7. ## 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. ## 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. ## 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. ## 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

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