Julian Date Codes compared to today()

[Doug E Rig]

I could sure use some help.
I have a worksheet, sheet1, containing item codes in one column, and in another is the number of days of shelf life that item has.

In another worksheet, sheet2, is a list of item codes in one column and the production date in another column shown as:
001A to 365A (or 366 for leap years). (Where 001 represents Jan 1, and 365 ordinarilly represents Dec 31.)

The Alpha character "A" in this example represents the year of manufacture.

I plan to build a table in another worksheet that defines the year where for example: (with the Alpha charcter in one column, and the year in an adjacent column.
A=2000
B=2001
C=2002
D=2006
E=2003
F=2004
G=2005

Therfore a date code of 059D represents Feb 28, 2006

(Note that 2006, and "D" appear out of sequence, but this was intentional as in the real data and sequence we did not include letter characters that could be missread like the letter O, vs. the number zero or the letter Q; or the letters V and W as they are too similar and could be mistaken.)

I plan to use a VLOOKUP in the in sheet2 to lookup the self life in sheet1.

Then in another column in sheet2 calulate the number of remaining days between the items date code and its shelf life looked up from sheet1.

How do you tie in all of this?

Thanks for any help I can get.
Doug

 

[Juan Pablo González]

To get the year you would do something like

A2:

=VLOOKUP(RIGHT("059D"), TableWithAlphaAndYears, 2, 0)

then you can get the "actual" date with

B2:

=DATE(A2, 1, 0) + LEFT("059D", 3)

then you can just do the difference between the two dates.

 

[Doug E Rig]

Juan Pablo,

I'm not understand this. (I'm sure your'e on target but I don't get it.)

Assume I have the following data in the sheet2 having the item code in A1, then the 059D in B1.

Then in C1 I have the Lookup formula returning say 730 days of total shelf life (from Sheet1).

Column D's formula would be what to convert the 059D to a numeric formated as a date showing 02/28/06 where the date codes conversion is in a table or sheet3.

Where in sheet3:
B2 contains the alpha code, ie "D"
C2 contains the year, ie "2006"

Please help me with what might just be a brain dead moment here on my end.
Doug

 

[Juan Pablo González]

The formula in D1 would be:

=DATE(VLOOKUP(RIGHT(B1),Sheet3!$B:$C,2,0),1,0)+LEFT(B1,3)

 

[Doug E Rig]

Jaun Pablo

Bless you! This is really great, . . and I'm always amazed how smart people are and how they discovered some of these tricks! (Makes me feel like I just got acquainted with Excel.)
Doug