Validating D.O.B

[adn4n]

Hi i have a cell where it contains D.O.B, say for instance..
24/06/1961, therefore the persons age would be 42.

How could i validate this cell so that the D.O.B cannot be entere
beyond 1985 (Therefore the person being younger than 18) I need
dialogue box to appear to let the user know that under 18's are no
employed by the company also.. to try again if they need to..

regards.

 

[Bob Phillips]

Not sure whether you want exactly 18, or 18 at the start of the year.

If the former us

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
With Target
If .Value > DateSerial(Year(Date) - 18, Month(Date), Day(Date))
Then
MsgBox "Candidate is under 18"
.Value = ""
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

If the latter use

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
With Target
If .Value > DateSerial(Year(Date) - 18, 1,1) Then
MsgBox "Candidate is under 18"
.Value = ""
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

This is worksheet code, so right-click the sheet tab, select View Code, and
paste the code in.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Norman Harker]

Hi adn4n!

One approach is to use a data validation on the date entry cell:

With date entry cell A1
Select A1
Data > Validation
Custom
=A1<=DATE(YEAR(TODAY())-18,MONTH(TODAY()),DAY(TODAY()))

Add appropriate error messages.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)

It is imperative that the patches provided by Microsoft in its April
Security Release be applied to Systems as soon as possible. It is
believed that the likelihood of a worm being released SOON that
exploits one of the vulnerabilities addressed by these patches is VERY
HIGH.
See:
http://www.microsoft.com/security/protect/