Subscribe Bookmark RSS Feed

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

joshcummins

Community Trekker

Joined:

Jul 11, 2014

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.

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson

Super User

Joined:

Jun 22, 2012

Solution

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
txnelson

Super User

Joined:

Jun 22, 2012

Solution

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

Joined:

Jul 11, 2014

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

Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

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

10815_JMPScreenSnapz001.png


-Jeff

-Jeff