Years Conversion in Numbers

This forum is a self-help system for the exchange of ideas among iWork users.

Years Conversion in Numbers

Postby RexinMinn » Fri Jan 28, 2011 2:09 pm

I am trying hard to make an attempt to switch over to Numbers for my various spreadsheet data bases, and am having some success. But one thing has come up I'd like to correct. When I copy and paste a listing of dates, such as "July 21, 1919" from Appleworks spreadsheet into a Numbers spreadsheet, it turns all my years preceding with "19" in a "20". So each of the 65 dates in my original list (birth years for deceased baseball players) displays a date exactly 100 years off. How can I fix this without going through each cell and repairing the data manually?
RexinMinn
 
Posts: 44
Joined: Sat Feb 07, 2009 8:09 am
Location: Mpls Area

Re: Years Conversion in Numbers

Postby Barry » Fri Jan 28, 2011 3:09 pm

RexinMinn wrote:I am trying hard to make an attempt to switch over to Numbers for my various spreadsheet data bases, and am having some success. But one thing has come up I'd like to correct. When I copy and paste a listing of dates, such as "July 21, 1919" from Appleworks spreadsheet into a Numbers spreadsheet, it turns all my years preceding with "19" in a "20". So each of the 65 dates in my original list (birth years for deceased baseball players) displays a date exactly 100 years off. How can I fix this without going through each cell and repairing the data manually?


Try this. The original date is in B2. The formula goes into a temporary new column added after column B.

=DATE(YEAR(B2)-100,MONTH(B2),DAY(B2))

Copy the result(s), then select the cells containing the original date(s) then
Go Edit > Paste Values.

Regards,
Barry
Barry
 
Posts: 1390
Joined: Mon Sep 30, 2002 12:54 am
Location: 48°50' N 123° 30'W

Re: Years Conversion in Numbers

Postby RexinMinn » Fri Jan 28, 2011 5:26 pm

Thanks for that, Barry. I'm not quite getting you.

First, what do you mean by "Paste Values" ?

I've played with your idea, but when I plug "-100" into Column C (I've placed the erroneous date in column B), and when I perform subtraction it subtracts the days. For example, I place "Dec. 12, 2022" in the column. When I subtract 100 I get Sept. 3, 2022, 100 days earlier. I want to get 100 years earlier.

Wouldn't I have to enter the years into a separate column and then use a formula?

Other than that, I'm at a loss aside from the "manual" idea. One would think there would be a way to reformat a year into a century other than the 21st. This kind of thing boggles my mind.
RexinMinn
 
Posts: 44
Joined: Sat Feb 07, 2009 8:09 am
Location: Mpls Area

Re: Years Conversion in Numbers

Postby Barry » Fri Jan 28, 2011 6:56 pm

RexinMinn wrote:Thanks for that, Barry. I'm not quite getting you.

First, what do you mean by "Paste Values" ?

I've played with your idea, but when I plug "-100" into Column C (I've placed the erroneous date in column B), and when I perform subtraction it subtracts the days.



Hi Rex,

Sorry. In a rush this morning and posted without re-reading the post (and without pasting in the formula for column C). See edited version above. "Paste Values" is an item in the Edit menu.

One would think there would be a way to reformat a year into a century other than the 21st. This kind of thing boggles my mind.


Mostly due to Numbers being a more recent application than AppleWorks, I suspect. Unless you actually specify the century when entering the date, the application and/or the system has to take a 'best guess'. The boundary for guessing "19"or "20" is either at a different place in each application, or is determined by the current year when the guess is made.

Regards,
Barry
Barry
 
Posts: 1390
Joined: Mon Sep 30, 2002 12:54 am
Location: 48°50' N 123° 30'W

Re: Years Conversion in Numbers

Postby RexinMinn » Fri Jan 28, 2011 10:37 pm

Gotcha, Barry,

Thanks.

I will likely perform the changes manually. NBD.
I appreciate your taking the time to respond.
Have a great weekend!

Rex
RexinMinn
 
Posts: 44
Joined: Sat Feb 07, 2009 8:09 am
Location: Mpls Area


Return to iWork Help

cron
AppleWorks Users Group Logo iWork Users Group Logo