Need help in adjusting date of birth year

[ChetForce]

I'm new here and to Excel, I hope I am at the right place.
Problem. I receive a file from QuickBooks that has the wrong century on some datesw of Birth.
DOB. Ex: 03/05/2028. I know why it is happening but I need to correct it in Excel.
I think it's as simple as having a function that would check the year and if it is 2000, then subt 100.
I have tried several times and exhausted my search for the correct answer. Any help here is greatly appreciated.

Chet

[jeffreybrown]

Hi Chet,

Let's say you date is in A1

If you just need to adjust the year then try

=DATE(1960,MONTH(A1),DAY(A1))

or if you want to check the date and if found, change it to something else

=DATE(IF(YEAR(A1)=2000,1960,""),MONTH(A1),DAY(A1))

[dilipandey]

Hi Chet,

Welcome to the forum.

Assuming you have the DOB in column A, use below formula in column B and drag it down:-

=IF(VALUE(LEFT(YEAR(A1),2))=20,DATE(YEAR(A1)-100,MONTH(A1),DAY(A1)),A1)

Regards,
DILIPandey

<click on below 'star' if this helps>

[martindwilson]

=if(year(a1)>=2000,date(year(a1)-100,month(a1),day(a1)),a1)

[Cutter]

Or

=IF(YEAR(A1)>2000,EDATE(A1,-1200),A1)

[ChetForce]

I can't beleive so many quick responses. Thanks a millon. I will try them all.

One thing I didn't point out may make a difference... My field is in col "I". Only want to change the year of 20. because it should have been 19 It is 100 years off. I wanted to do the change and keep it in "I" . If I create a new col, then only the changed gets to the new col.

Thanks to all that resonded, I will get back and let you know how I work it out.

Chet