Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculate date of birth with an end date and age | Excel Worksheet Functions | |||
Birth date from age | Excel Worksheet Functions | |||
How can I get an age using todays date and date of birth? | Excel Discussion (Misc queries) | |||
formula to calculate age using birth date and current date | Excel Worksheet Functions |