Doug E Rig
New Member
- Joined
- Jan 2, 2003
- Messages
- 40
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
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