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
--
Jeff C
Live Well .. Be Happy In All You Do

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.
--
Ken Hudson


"Jeff C" wrote:

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
--
Jeff C
Live Well .. Be Happy In All You Do

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.
--
Gary''s Student - gsnu200781


"Jeff C" wrote:

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
--
Jeff C
Live Well .. Be Happy In All You Do

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.
--
HTH...

Jim Thomlinson


"Jeff C" wrote:

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
--
Jeff C
Live Well .. Be Happy In All You Do

I would be concerned that if I looked at the same worksheet next month, the
revisions would be different (since TODAY() is volatile).
--
Gary''s Student - gsnu200781


"Jim Thomlinson" wrote:

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.
--
HTH...

Jim Thomlinson


"Jeff C" wrote:

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
--
Jeff C
Live Well .. Be Happy In All You Do

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.
--
HTH...

Jim Thomlinson


"Gary''s Student" wrote:

I would be concerned that if I looked at the same worksheet next month, the
revisions would be different (since TODAY() is volatile).
--
Gary''s Student - gsnu200781


"Jim Thomlinson" wrote:

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.
--
HTH...

Jim Thomlinson


"Jeff C" wrote:

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
--
Jeff C
Live Well .. Be Happy In All You Do

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


"Ken Hudson" wrote:

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

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

I appreciate your response

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.
--
Ken Hudson


"Jeff C" wrote:

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
--
Jeff C
Live Well .. Be Happy In All You Do