I admit I'm confused by this. I'm using Excel '97 (Yes, yes, I know. Upgrade.
But this is a client's system over which I have no control.)

The client supplied me with a spreadsheet containing a lot of data. I need
to make the data consistent for it to be usable. There is a column containing
only the year which is currently defined as Text. I want the data to be Date
type with a "YYYY" format.

I managed to do this somehow in another spreadsheet of similar data. In the
spreadsheet I'm currently working on, when I change the column to Date type
with "YYYY" format, the value in the cell is wrong.

Example: The value in the cell should be "1994." When I change the type to
Date format "YYYY" and enter "1994" in the cell, the formula bar displays
"6/16/05" and the cell display is "1905." If I type "1995" in the cell, the
formula bar displays "6/17/05."

I wasn't even sure how to phrase this question for the KB. Any ideas? Thanks.

On Tue, 11 Oct 2005 11:05:13 -0700, Blaise
wrote:

I admit I'm confused by this. I'm using Excel '97 (Yes, yes, I know. Upgrade.
But this is a client's system over which I have no control.)

The client supplied me with a spreadsheet containing a lot of data. I need
to make the data consistent for it to be usable. There is a column containing
only the year which is currently defined as Text. I want the data to be Date
type with a "YYYY" format.

I managed to do this somehow in another spreadsheet of similar data. In the
spreadsheet I'm currently working on, when I change the column to Date type
with "YYYY" format, the value in the cell is wrong.

Example: The value in the cell should be "1994." When I change the type to
Date format "YYYY" and enter "1994" in the cell, the formula bar displays
"6/16/05" and the cell display is "1905." If I type "1995" in the cell, the
formula bar displays "6/17/05."

I wasn't even sure how to phrase this question for the KB. Any ideas? Thanks.

Excel's stores dates as the number of days since 1/1/1900.

When you change the format to date, and enter 1994, Excel dutifully computes
the date that is 1,994 days from 1/1/1900 which turns out to be 16 June 1905.

If you want to use the DATE format, you must enter Dates!

An alternative would be to convert those years to some date in the relevant
year. So, for example, if A1 contains the year, you could set up another
column with the formula =DATE(A1,1,1) That would convert the date to 1 Jan
1994 (or whatever year is in A1). You could then copy/paste values over your
original, and delete the "helper column".


--ron

Ron,

Thanks for your very helpful reply. I think the reason it worked before is
that I changed the cell format but didn't actually type in the cell. Your
description of what Excel is doing makes sense. Well, for Excel it makes
sense. :)

For my purposes, I can use the Number type. It doesn't have to be Date type.
I was just stuck on the fact that "1994" is a year in the human context, so
tried to format it that way.

Thanks again.

Blaise

"Ron Rosenfeld" wrote:

On Tue, 11 Oct 2005 11:05:13 -0700, Blaise
wrote:

I admit I'm confused by this. I'm using Excel '97 (Yes, yes, I know. Upgrade.
But this is a client's system over which I have no control.)

The client supplied me with a spreadsheet containing a lot of data. I need
to make the data consistent for it to be usable. There is a column containing
only the year which is currently defined as Text. I want the data to be Date
type with a "YYYY" format.

I managed to do this somehow in another spreadsheet of similar data. In the
spreadsheet I'm currently working on, when I change the column to Date type
with "YYYY" format, the value in the cell is wrong.

Example: The value in the cell should be "1994." When I change the type to
Date format "YYYY" and enter "1994" in the cell, the formula bar displays
"6/16/05" and the cell display is "1905." If I type "1995" in the cell, the
formula bar displays "6/17/05."

I wasn't even sure how to phrase this question for the KB. Any ideas? Thanks.

Excel's stores dates as the number of days since 1/1/1900.

When you change the format to date, and enter 1994, Excel dutifully computes
the date that is 1,994 days from 1/1/1900 which turns out to be 16 June 1905.

If you want to use the DATE format, you must enter Dates!

An alternative would be to convert those years to some date in the relevant
year. So, for example, if A1 contains the year, you could set up another
column with the formula =DATE(A1,1,1) That would convert the date to 1 Jan
1994 (or whatever year is in A1). You could then copy/paste values over your
original, and delete the "helper column".


--ron

On Tue, 11 Oct 2005 13:05:05 -0700, Blaise
wrote:

Ron,

Thanks for your very helpful reply. I think the reason it worked before is
that I changed the cell format but didn't actually type in the cell. Your
description of what Excel is doing makes sense. Well, for Excel it makes
sense. :)

For my purposes, I can use the Number type. It doesn't have to be Date type.
I was just stuck on the fact that "1994" is a year in the human context, so
tried to format it that way.

Thanks again.

Blaise

You're welcome. Glad to help.

--ron