How to add 100 years to a column of dates

's Computer Specifications

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?

's Computer Specifications

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
--
AP

"Albert Einstein" <(E-Mail Removed)> a écrit dans le message de news:
(E-Mail Removed)...
>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?

's Computer Specifications

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

"Ardus Petus" <(E-Mail Removed)> a écrit dans le message de news:
(E-Mail Removed)...
> 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
> --
> AP
>
> "Albert Einstein" <(E-Mail Removed)> a écrit dans le message de news:
> (E-Mail Removed)...
>>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?
>
>

's Computer Specifications

Thank you.

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
all dates in a column have gone back four years	benadem	Microsoft Excel Worksheet Functions	2	28th Jul 2008 08:21 AM
Check dates in spreadsheet against expiry years column and change colour	OHSGoddess@gmail.com	Microsoft Excel Worksheet Functions	4	22nd Jan 2007 05:14 AM
How do I subtract dates to get a number in years or years & month	=?Utf-8?B?anVkZQ==?=	Microsoft Excel Misc	2	25th Aug 2006 08:02 PM
How do I Fill a column with dates (for 3 years of daily data)	=?Utf-8?B?QXBwbGVfcGk=?=	Microsoft Excel Misc	1	18th Feb 2006 08:48 PM
Calculating dates - Need to calculate years and months between source dates.	jmw748	Microsoft Access Queries	2	7th Jan 2004 12:31 PM