Returning a result from a single charater within a cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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.
 
Roger, I need to ask your assistance again, if you don't mind.

I found that your formula works fine, but when a lot number manufactured in
year 2000 or later is processed (for example, ABC02D123-456 - this is a lot
manufactured in April 2002), the value returned is "04/01/1928". Is there a
way to account for this glitch?

Thanks again for all your help.

Bill K
 
Bill,

I just tested Roger's formula. It returns 4/1/1902, not 1928.

If you want to account for the Y2K problem, say for dates past 1980,
you can use the following variant of Roger's formula:

=DATE(MID(A1,4,2)+1900+100*(--MID(A1,4,2)<80),CODE(MID(A1,6,1))-64-1*(MID(A1,6,1)>"I"),1)

As for the 1928, I cannot explain it. The 1904 system obviously is not
the reason.

HTH
Kostis Vezerides
 
thank you very much. Your adjusted formula worked great!

Good thing God made smart people like you to watch out for dumb people like
me!
 
Back
Top