How to add 100 years to a column of dates

A

Albert Einstein

I imported a faxed spreadsheet into OmniPage and directed it to output
the data into an Excel spreadsheet.

It did a good job, except that the dates in the date column were all
changed into early twentieth century dates.

Instead of adding 100 years to each date one-by-one, can I add 100 years
to the entire column with one command?
 
A

Ardus Petus

In a staging column, enter formula:
=DATE'YEAR(A1)+100,MONTH(A1),DAY(A1))
drag down.
When finished, Copy that column and Paste Special Values over original
column.

HTH
 
A

Ardus Petus

Typo:
=DATE(YEAR(A1)+100,MONTH(A1),DAY(A1))

Ardus Petus said:
In a staging column, enter formula:
=DATE'YEAR(A1)+100,MONTH(A1),DAY(A1))
drag down.
When finished, Copy that column and Paste Special Values over original
column.

HTH
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Counting number of days in several periods ignoring repeating dates 3
How do I add 1 to a number in column b 8
year formula 1
Date Filling for Prior Year 8
Synchronization problems while moving folders to new email 1
Adding variable number of columns 8
Having a date column with just month and year 11
Format dates & add ten years to date 1

Top