Date Validation for Date of Birth
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 |
Date Validation for Date of Birth
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) "Stitch45" wrote in message .uk... 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 |
Date Validation for Date of Birth
Probably not necessary. In Windows, go to Control Panel, Regional options,
Customize, Date. There you can choose how Windows interprets 2-digit years. -- Kind regards, Niek Otten "Stitch45" wrote in message .uk... 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 |
All times are GMT +1. The time now is 09:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com