Returning a result from a single charater within a cell
Carlo, hank you very much for your response. I returned an error message
when i attempted to use your formula. But the response from Roger worked
fine, it appears to be using the US standard date format.
Thanks again for your response.
"Carlo" wrote:
hi Bill
try following:
=TEXT("01-" & RIGHT("0" &
IF(CODE(MID(A1;6;1))73;CODE(MID(A1;6;1))-65;CODE(MID(A1;6;1))-64);2) & "-" &
MID(A1;4;2);"mm/dd/yy")
perhaps you have to change somethings, as i am working with European Date
Greetings
Carlo
"Bill K" wrote:
I have a spreadsheet with a column devoted to lot numbers. The lot numbers
contain information identifying the date of manufacture. I would like to set
up another column where the date of manufacture is displayed from info taken
from the lot number column.
The numbers are of the format:
ABC85J123D456
or
AB-85J123D456
The fourth and fifth positions represent the year of manufacture, and the
sixth position represents the month ("A" is Jan, "B" is Feb, etc -
incidentally, "I" is skipped because it can be mistaken for a "1").
I would like my date column to look at the 4th through 6th positions in the
lot number, and return a date in format "mm/dd/yy", where the "dd" equals
"01" (the day isn't important to my spreadsheet).
Can someone assist? And, thanks in advance.
Bill K
|