Decipher 10 digit string to get date

[tony0710]

Hi

I got codes that have 10 digits: e.g. 0921453591

From which:

0921453591 = dispatch - to be ignored.
0921453591 = date - 21st
0921453591 = month - 4 - (April)
0921453591 = year - 5 - 2005

so the figures from code will be: 21/4/5 - which is then interpreted as a date 21/04/05

Months - 1=jan, 2=feb etc X=oct, Y=nov, Z=dec

e.g. 0913Y53774 = date 13/11/05

So if the code is placed into col A, what formula/string/syntax do I use to get the date col B automatically?

All help will be much appreciated.

[NBVC]

Try:

Please Login or Register  to view this content.

where A1 = code cell

You may have to format cell to appear as date.

Note:

This assumes dates within year range 2000 and 2009. What is code for year 2010 or 2011 or 1999, etc... Are these possible?

[sweep]

Hi,

Try this:

=DATEVALUE(MID(A1,3,2)&"/"&MATCH(MID(A1,5,1),{"1","2","3","4","5","6","7","8","9","X","Y","Z"},0)&"/"&"200"&MID(A1,6,1))

[tony0710]

Hi

NBVC quoted:

This assumes dates within year range 2000 and 2009. What is code for year 2010 or 2011 or 1999, etc... Are these possible?

Answer:

The date found by the codes is the actual packaging date - the shelf life of the product is 3 years from the date of package.

One MAJOR assumption is being made here - that is that the reptuable company doesn't dispatch goods out of date. So with this in mind: if I get a code showing the year as 5 and that the current year is 2009 - then the dispatch date has to be the first year ending in "5" prior to 2009 - which will be 2005. Bearing in mind that the shelf life is 3 years - product expires in 2008 - so it has expired!! That is what I'm trying to work out! Are the company sending me goods out of date or am I not rotating my stock?

Hope that explains.

Also is it possible to put that into the code?? Interesting???

[NBVC]

Hi

NBVC quoted:



Answer:

The date found by the codes is the actual packaging date - the shelf life of the product is 3 years from the date of package.

One MAJOR assumption is being made here - that is that the reptuable company doesn't dispatch goods out of date. So with this in mind: if I get a code showing the year as 5 and that the current year is 2009 - then the dispatch date has to be the first year ending in "5" prior to 2009 - which will be 2005. Bearing in mind that the shelf life is 3 years - product expires in 2008 - so it has expired!! That is what I'm trying to work out! Are the company sending me goods out of date or am I not rotating my stock?

Hope that explains.

Also is it possible to put that into the code?? Interesting???

Do you mean something like this:

Please Login or Register  to view this content.

[tony0710]

wow

That works a treat - though I would still like to see the packaging date.

Col A: code
Col B: Packaing date
Col C: Expired or not

Better still if the whole even can be updated daily, so that perhaps Col C could say something along the lines "X number of days to expire" or "Y number of days expired"

Sorry to be a pain!!

[tony0710]

*** bumped ***

[Leith Ross]

Hello tony0710,

Your original question has been answered. You are now asking for more information that was not included in your original post. You need to make a new post and include all data code that pertains to your question. This thread will be closed. Please read the forum's rules on posting.