Replace update year in date

J

Jeff C

because of data entry errors I have a large number of birthdates that have
been entered with the year 20**instead of 19**. These are formatted in date
format and I am using Excel 2003 in XP Pro.

Can someone suggest a way of correcting this?

Thanks in advance
 
K

Ken Hudson

Hi Jeff,

One way....
If your dates are in column A and column B is empty, enter this formula in
B1 and copy down.

=MONTH(A1) & "/" & DAY(A1) & "/" & YEAR(A1)-100

Then copy and paste special > values to get rid of the formulas and then you
can delete column A.

Be sure to make a copy of your sheet first.
 
G

Gary''s Student

If your dates are in column A, then in an un-used column, enter:

=DATE("19" & RIGHT(YEAR(A1),2),MONTH(A1),DAY(A1)) and copy down

this will move everything back to the 20th century:
2084 will become 1984
2020 will become 1920

etc.
 
J

Jim Thomlinson

Something like this should do.

=IF(YEAR(A1)>YEAR(TODAY()),A1,DATE(YEAR(A1)-100,MONTH(A1),DAY(A1)))

It fixes the year if the year is some time in the future... The date is in A1.
 
G

Gary''s Student

I would be concerned that if I looked at the same worksheet next month, the
revisions would be different (since TODAY() is volatile).
 
J

Jim Thomlinson

My formula only modifies the output if the date entered is a year some time
in the future. Even though today is volatile so long as the ages entered are
for people who are already born the formula should always return the same
result. Your formula assumes that everyone was born in 19XX which may not be
the case.

As you upoint our though Today() is volatile and comes with some inherant
overhead. The OP may want to copy and paste special values if the spreadsheet
performance suffers.
 
J

Jeff C

--
Jeff C
Live Well .. Be Happy In All You Do


Ken Hudson said:
Hi Jeff,

One way....
If your dates are in column A and column B is empty, enter this formula in
B1 and copy down.

=MONTH(A1) & "/" & DAY(A1) & "/" & YEAR(A1)-100
Click to expand...

Thanks Ken - Just what I was looking for - and now to go correct the data
entry clerks!

I appreciate your response
 
Top