Alphanumeric into a Date

cochrank

Hello!

I need to extract a date from an alphanumeric prefix of a serial number. The entire serial number follows the format A0225-XXXX-XXXX, however, only the prefix A0225 corresponds to the date that I want to extract. The remaining, X's are autogenerated and are meaningless to me for this question.

The first letter (A) corresponds to the month, the next two digits (02) are the day of the month, and the final two digits (25) are the shortened year. In the example above (A0225): A=January, 02=second day of the month, 25=year 2025. Therefore, the date is 01/02/2025 (following the format MM/DD/YYYY).

In SmartSheet, I have column "Serial Number" and then I have column "Date." I need a formula in my column "Date" that will extract the date from that alphanumeric prefix of the column "Serial Number." I know how to use the LEFT, MID, RIGHT formulas, but I'm uncertain how to combine them all together to make a date and then how to convert the letter to a number.

Thank you so much, I appreciate any help!

Kelly Moore

Hey @cochrank

try this:

=DATE(VALUE(MID([Serial Number]@row, 4, 2)) + 2000, IF(LEFT([Serial Number]@row, 1) = "A", 1, IF(LEFT([Serial Number]@row, 1) = "B", 2, IF(LEFT([Serial Number]@row, 1) = "C", 3, IF(LEFT([Serial Number]@row, 1) = "D", 4, IF(LEFT([Serial Number]@row, 1) = "E", 5, IF(LEFT([Serial Number]@row, 1) = "F", 6, IF(LEFT([Serial Number]@row, 1) = "G", 7, IF(LEFT([Serial Number]@row, 1) = "H", 8, IF(LEFT([Serial Number]@row, 1) = "I", 9, IF(LEFT([Serial Number]@row, 1) = "J", 10, IF(LEFT([Serial Number]@row, 1) = "K", 11, IF(LEFT([Serial Number]@row, 1) = "L", 12)))))))))))), VALUE(MID([Serial Number]@row, 2, 2)))

Will this work for you?
Kelly

cochrank

I needed some extra parenthesis, but this worked!! Thank you so much!