Extracting a date from a line of data

Katy H
Katy H ✭✭✭✭✭✭
edited 06/17/22 in Formulas and Functions

I have been working on formulas to automate data extraction from a string of data that is used to identify lines of data being exported from equipment and I am struggling with how to extract the date and convert it into a regular date format. Any help would be appreciated!

Example line of data: A3146 GAT 26MAY22 CCV21

The date is bolded, I have figured out how to extract everything else and know I will need to use helper columns. I know how to convert the date but not day and year.

Katy Hall

Head of Product Management

ILLA Canna


Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    If you are already able to extract the string, you would do something like this...

    =DATE(VALUE("20" + RIGHT(extraction_formula, 2)), month_options_below, VALUE(LEFT(extraction_formula, 2)))

    This is assuming that the days will always be two digits so that 1 is actually "01".

    You are going to want to drop your extraction formula into the two places where it says "extraction_formula".

    For the "month_options_below" portion you have two options. You can either write out a nested IF statement to grab the month text and output the appropriate month number or you can create a reference table to has the month text in one column and the month number in another and use an INDEX/MATCH to pull in the appropriate month number.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!