BookmarkSubscribe
Choose Language Hide Translation Bar
joshcummins
Community Trekker

Using formula editor to reformat lot numbers to date format in a separate column.

Is there a way to use the formula editor to reformat a lot number to date format in a separate column? The lot numbers are formatted as such: MMYYDD.B where "B" is the # of the batch for day. I would like to be able to create a formula that takes the lot number and converts it to the following date format: MM/DD/YY or MM/DD/YYYY.

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Using formula editor to reformat lot numbers to date format in a separate column.

create the new column.  Enter in the following format:

   Date MDY(Num(Substr(lotid, 1, 2)), Num(Substr(lotid, 5, 2)), Num(Substr(lotid, 3, 2)))

Finally, go to the Column Info for the new column and specify to use the date format

   m/d/y

10798_pastedImage_0.png

10799_pastedImage_0.png

Jim
3 REPLIES 3
txnelson
Super User

Re: Using formula editor to reformat lot numbers to date format in a separate column.

create the new column.  Enter in the following format:

   Date MDY(Num(Substr(lotid, 1, 2)), Num(Substr(lotid, 5, 2)), Num(Substr(lotid, 3, 2)))

Finally, go to the Column Info for the new column and specify to use the date format

   m/d/y

10798_pastedImage_0.png

10799_pastedImage_0.png

Jim
joshcummins
Community Trekker

Re: Using formula editor to reformat lot numbers to date format in a separate column.

Thanks for this. It worked as intended. Also, thanks for the fast turnaround time.

Josh Cummins

Analytical Chemist

Dynaloy LLC A subsidiary of Eastman Chemical Company

W: +1.423.229.8771

M: +1.317.223.3487

0 Kudos
Jeff_Perkinson
Community Manager Community Manager

Re: Using formula editor to reformat lot numbers to date format in a separate column.

The Word() and Informat() functions make this pretty easy.

10815_JMPScreenSnapz001.png


-Jeff

-Jeff