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
=DATE(MID(A1,4,2),CODE(MID(A1,6,1))-64-1*(MID(A1,6,1)"I"),1)

--
Regards

Roger Govier


"Bill K" <Bill wrote in message
...
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

"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

Roger, thank you very much! That worked like a charm.

"Roger Govier" wrote:

Hi Bill

Try
=DATE(MID(A1,4,2),CODE(MID(A1,6,1))-64-1*(MID(A1,6,1)"I"),1)

--
Regards

Roger Govier


"Bill K" <Bill wrote in message
...
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

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

Hi Bill

You're very welcome. Thanks for the feedback

--
Regards

Roger Govier


"Bill K" wrote in message
...
Roger, thank you very much! That worked like a charm.

"Roger Govier" wrote:

Hi Bill

Try
=DATE(MID(A1,4,2),CODE(MID(A1,6,1))-64-1*(MID(A1,6,1)"I"),1)

--
Regards

Roger Govier


"Bill K" <Bill wrote in message
...
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

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

"Roger Govier" wrote:

Hi Bill

You're very welcome. Thanks for the feedback

--
Regards

Roger Govier


"Bill K" wrote in message
...
Roger, thank you very much! That worked like a charm.

"Roger Govier" wrote:

Hi Bill

Try
=DATE(MID(A1,4,2),CODE(MID(A1,6,1))-64-1*(MID(A1,6,1)"I"),1)

--
Regards

Roger Govier


"Bill K" <Bill wrote in message
...
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

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


Bill K wrote:
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

"Roger Govier" wrote:

Hi Bill

You're very welcome. Thanks for the feedback

--
Regards

Roger Govier


"Bill K" wrote in message
...
Roger, thank you very much! That worked like a charm.

"Roger Govier" wrote:

Hi Bill

Try
=DATE(MID(A1,4,2),CODE(MID(A1,6,1))-64-1*(MID(A1,6,1)"I"),1)

--
Regards

Roger Govier


"Bill K" <Bill wrote in message
...
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

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!

"vezerid" wrote:

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


Bill K wrote:
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

"Roger Govier" wrote:

Hi Bill

You're very welcome. Thanks for the feedback

--
Regards

Roger Govier


"Bill K" wrote in message
...
Roger, thank you very much! That worked like a charm.

"Roger Govier" wrote:

Hi Bill

Try
=DATE(MID(A1,4,2),CODE(MID(A1,6,1))-64-1*(MID(A1,6,1)"I"),1)

--
Regards

Roger Govier


"Bill K" <Bill wrote in message
...
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