Parsing A Code Back into Useable information

[Eric Deneault]

I have a code 0ddmyLxx where after the leading 0 is the day, month, year, location;xx is a machine number. The Location is constant as "S".
The year is simple by added the number to 2000, and the months of the year by convention are: 1,2,3,4,5,6,7,9, X, N,D where X is Oct, N is Nov and D is Dec

I have used Left & Right functions to parse rebuild an actual date
24-May-2008 but the constructed date is still not real - how do I make it so - glad for any help on this one.

Thank You
Eric

 

[RoryA]

If you include the parsed sections in the DATE function, you will get a real date - i.e.
=DATE(year_bit,month_bit, day_bit)

 

[Eric Deneault]

Rory thank you Tip for the here is the outcome -Eric



<TABLE style="WIDTH: 800pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1066 border=0 x:str><COLGROUP><COL style="WIDTH: 140pt; mso-width-source: userset; mso-width-alt: 6838" width=187><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><COL style="WIDTH: 187pt; mso-width-source: userset; mso-width-alt: 9106" width=249><COL style="WIDTH: 138pt; mso-width-source: userset; mso-width-alt: 6729" width=184><COL style="WIDTH: 158pt; mso-width-source: userset; mso-width-alt: 7680" width=210><TBODY><TR style="HEIGHT: 39.75pt; mso-height-source: userset" height=53><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 140pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 39.75pt; BACKGROUND-COLOR: transparent" width=187 height=53>Colum M</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 84pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>Column N</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 93pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=124>Data Column O</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 187pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=249>Column AA</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 138pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=184>Column AC</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 158pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=210>Column AE</TD></TR><TR style="HEIGHT: 72pt" height=96><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 140pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 72pt; BACKGROUND-COLOR: transparent" width=187 height=96>IF(AE3="","?",+AE3)</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 84pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=112 x:str="'+AC3">+AC3</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 93pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=124>00298S22</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 187pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=249>IF(MID(O3,4,1)="X",10,IF(MID(O3,4,1)="N",11,IF(MID(O3,4,1)="D",12,VALUE(MID(O3,4,1)))))</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 138pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=184>DATE((2000+MID(O3,5,1)),AA3,MID(O3,2,2))</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 158pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=210>MID(O3,FIND("S",O3)+1,2)</TD></TR></TBODY></TABLE>