I have a spreasheet that has account numbers in this format:
10000003-001-001-06-04.

The last pair of numbers are the year in 2 digit format. After I convert the
text string to columns using Date Text to Columns Delimited or Fixed
Width, I want to format the resulting 2 digit years to 4 digit. Using the
above account number as an example, the function result would be:
10000003 001 001 06 04

and I want it to format as:
10000003 001 001 2006 2004.

Now the tricky part. Some of the years in the account numbers of 20th
century, so the function out put would be:
10000114 001 001 96 91

which I want to format as:
10000114 001 001 1996 1991

Or an account out of:
182 10034877 182 002 01 99

which I want to format as:
182 10034877 182 002 2001 1999

I can set the format manually using custom, but that would require my
sorting the spreadsheet multiple times. Not my idea of a fund day when there
are 41,000+ records to format.

Suggestions, besides FIA?
--
I know enuff to be dangerous.

On Wed, 13 Dec 2006 11:15:00 -0800, RealGomer
wrote:

The last pair of numbers are the year in 2 digit format. After I convert the
text string to columns using Date Text to Columns Delimited or Fixed
Width, I want to format the resulting 2 digit years to 4 digit. Using the
above account number as an example, the function result would be:
10000003 001 001 06 04

and I want it to format as:
10000003 001 001 2006 2004.

I note you write you want to "format" the value, not "change" the value.

To format the cell so it will show appropriate four digit date:

Format/Cells/Number/Custom Type: [30]"19"00;"20"00

If the two digit year is 30, the cell will show 19xx ; if <=30, then the cell
will show 20xx.

So your 4 will show as 2004.

If you want to actually change the value, then you need a function:

=A1+1900+100*(A1<=30)

Substitute the cell where your two year value is for A1.
--ron

Let's say the two-digit year is in column E. We need to make a rule on
mapping dates into centuries .Arbitrarily 30 will be the division point.

So 29 will mean 2029
So 30 will mean 1930

Pick another point if you like. In F1 enter:

=IF(E129,1900+E1,2000+E1)

and there's your four digit date!
--
Gary's Student


"RealGomer" wrote:

I have a spreasheet that has account numbers in this format:
10000003-001-001-06-04.

The last pair of numbers are the year in 2 digit format. After I convert the
text string to columns using Date Text to Columns Delimited or Fixed
Width, I want to format the resulting 2 digit years to 4 digit. Using the
above account number as an example, the function result would be:
10000003 001 001 06 04

and I want it to format as:
10000003 001 001 2006 2004.

Now the tricky part. Some of the years in the account numbers of 20th
century, so the function out put would be:
10000114 001 001 96 91

which I want to format as:
10000114 001 001 1996 1991

Or an account out of:
182 10034877 182 002 01 99

which I want to format as:
182 10034877 182 002 2001 1999

I can set the format manually using custom, but that would require my
sorting the spreadsheet multiple times. Not my idea of a fund day when there
are 41,000+ records to format.

Suggestions, besides FIA?
--
I know enuff to be dangerous.

Thank you for your advice. To me, format means how it looks and works, not
just cosmetic changes. You know, the true meaning of the word.
I'll give your solution a shot. It's a lot easier than trying to get our
software vendor to fix the code. We've been waiting four years for them to
get simple report fixed.
--
I know enuff to be dangerous.


"Ron Rosenfeld" wrote:

On Wed, 13 Dec 2006 11:15:00 -0800, RealGomer
wrote:

The last pair of numbers are the year in 2 digit format. After I convert the
text string to columns using Date Text to Columns Delimited or Fixed
Width, I want to format the resulting 2 digit years to 4 digit. Using the
above account number as an example, the function result would be:
10000003 001 001 06 04

and I want it to format as:
10000003 001 001 2006 2004.

I note you write you want to "format" the value, not "change" the value.

To format the cell so it will show appropriate four digit date:

Format/Cells/Number/Custom Type: [30]"19"00;"20"00

If the two digit year is 30, the cell will show 19xx ; if <=30, then the cell
will show 20xx.

So your 4 will show as 2004.

If you want to actually change the value, then you need a function:

=A1+1900+100*(A1<=30)

Substitute the cell where your two year value is for A1.
--ron

On Wed, 13 Dec 2006 15:56:00 -0800, RealGomer
wrote:

Thank you for your advice. To me, format means how it looks and works, not
just cosmetic changes. You know, the true meaning of the word.
I'll give your solution a shot. It's a lot easier than trying to get our
software vendor to fix the code. We've been waiting four years for them to
get simple report fixed.
--
I know enuff to be dangerous.

It's helpful to use the same language. That's why I asked.

From Excel HELP: You can use number formats to change the appearance of
numbers, including dates and times, without changing the number behind the
appearance. The number format does not affect the actual cell value that
Microsoft Excel uses to perform calculations. The actual value is displayed in
the formula bar.

In other words, in Excel, which is what we are discussing, format changes the
appearance, but not the content of the cell.

That is why I gave you two different solutions. The Format solution changes
the appearance of the cell; the formula solution gives you a new value to work
with.
--ron

Try =YEAR("1/1/"&A1)
--
David Biddulph

"RealGomer" wrote in message
...
I have a spreasheet that has account numbers in this format:
10000003-001-001-06-04.

The last pair of numbers are the year in 2 digit format. After I convert
the
text string to columns using Date Text to Columns Delimited or Fixed
Width, I want to format the resulting 2 digit years to 4 digit. Using the
above account number as an example, the function result would be:
10000003 001 001 06 04

and I want it to format as:
10000003 001 001 2006 2004.

Now the tricky part. Some of the years in the account numbers of 20th
century, so the function out put would be:
10000114 001 001 96 91

which I want to format as:
10000114 001 001 1996 1991

Or an account out of:
182 10034877 182 002 01 99

which I want to format as:
182 10034877 182 002 2001 1999

I can set the format manually using custom, but that would require my
sorting the spreadsheet multiple times. Not my idea of a fund day when
there
are 41,000+ records to format.

Suggestions, besides FIA?
--
I know enuff to be dangerous.