Date Validation for Date of Birth

[Stitch45]

I receive weekly excel files where the Date of Birth is shown as 01-Jan-26
for example, but when formatted to dd/mm/yyyy the data actually shows the
year as 2026. Can someone help with a VBA script that I can add to my
macro to correct these years to 1900s.

If activecell.value > Year(Now) then
activecell.value = ???

Any help/other suggestions appreciated

thanks

 

[Bob Phillips]

Give this a try


iLastRow = Cells(Rows.Count,"A").End(xlUp).Row
For i = 1 To iLastRow
With Cells(i,"A")
If IsDate(.Value) Then
If .Value > Date Then
.Value = DateSerial(Year(.Value -1000), Month(.value),
Day(.Value))
End If
End If
End With
Next i

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Niek Otten]

Probably not necessary. In Windows, go to Control Panel, Regional options,
Customize, Date. There you can choose how Windows interprets 2-digit years.