Format 2 digit year to 4 digit
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.
|