Covert string to date, etc
 

Hi guys, just can't figure this out:

1. Suppose I have 100 rows of data, rows are such as:
"ABMAR99", "ABMAR02", "ABDEC03", etc.

How to convert for example, "ABMAR99" to the first wednesday
in March 1999, which is, "1999-03-03"?

2. Suppose other than rows like "ABMAR99", "ABMAR02", "ABDEC03", I also
have rows like "AB_MLTE", "ABMAR2C0",etc, and I only want to keep the
rows
in the format ("ABMAR99", "ABMAR02", "ABDEC03").

In other words:

(1) I only want to keep rows with the exact length of 7.
(2) For rows with the exact length of 7, I want to delete rows whose
third letter is "_".

Thanks a lot, appreciate your help!

Covert string to date, etc
 

Hi,
Suppose, Column A contains that date code, Column B will be the helper
column, and Column C will be the answer

A2: ABMAR99
at B2, type the following formula:
=IF(OR(LEN(A2)7,MID(A2,3,1)="_"),"please
delete",DATE(IF(VALUE(RIGHT(A2,2))<=20,2000+RIGHT( A2,2),1900+RIGHT(A2,2)),VLOOKUP(MID(A2,3,3),{"jan" ,1;"feb",2;"mar",3;"apr",4;"may",5;"jun",6;"jul",7 ;"aug",8;"sep",9;"oct",10;"nov",11;"dec",12},2,FAL SE),1))

at C2, type the following formula:
=IF(B2="please
delete","",TEXT(IF(WEEKDAY(B2,1)<=4,DATE(YEAR(B2), MONTH(B2),5-WEEKDAY(B2,1)),DATE(YEAR(B2),MONTH(B2),12-WEEKDAY(B2,1))),"yyyy-mm-dd"))

then sort the helper column, and delete the rows

Bobocat

"MCI" wrote in message
ups.com...
Hi guys, just can't figure this out:

1. Suppose I have 100 rows of data, rows are such as:
"ABMAR99", "ABMAR02", "ABDEC03", etc.

How to convert for example, "ABMAR99" to the first wednesday
in March 1999, which is, "1999-03-03"?

2. Suppose other than rows like "ABMAR99", "ABMAR02", "ABDEC03", I also
have rows like "AB_MLTE", "ABMAR2C0",etc, and I only want to keep the
rows
in the format ("ABMAR99", "ABMAR02", "ABDEC03").

In other words:

(1) I only want to keep rows with the exact length of 7.
(2) For rows with the exact length of 7, I want to delete rows whose
third letter is "_".

Thanks a lot, appreciate your help!