Extract date from cell into helper column

Options

May 23 2023 02:53PM

This is how the date looks when it comes in, (it is pulled in from a 3rd party software via data shuttle). Trying to convert this into a helper column in this format: 5/23/2023 (removing the time stamp) so that I can us it in a report. Any help is greatly appreciated.

Thank you,

Brandon

Tags:

Best Answer

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

    You would need something like this:

    =DATE(VALUE(MID([Column Name]@row, FIND(" ", [Column Name]@row, FIND(" ", [Column Name]@row) + 1) + 1, 4)), nested_if, VALUE(MID([Column Name]@row, FIND(" ", [Column Name]@row) + 1, 2)))


    Where it says "nested_if" above, you will need to drop in a nested IF statement that looks at the month text and converts it into a number.

    =IF(LEFT([Column Name]@row, 3) = "Jan", 1, IF(LEFT([Column Name]@row, 3) = "Feb", 2, IF(LEFT([Column name]@row, 3) = "Mar", 3, .....................................))))))))))))

Answers

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

    You would need something like this:

    =DATE(VALUE(MID([Column Name]@row, FIND(" ", [Column Name]@row, FIND(" ", [Column Name]@row) + 1) + 1, 4)), nested_if, VALUE(MID([Column Name]@row, FIND(" ", [Column Name]@row) + 1, 2)))


    Where it says "nested_if" above, you will need to drop in a nested IF statement that looks at the month text and converts it into a number.

    =IF(LEFT([Column Name]@row, 3) = "Jan", 1, IF(LEFT([Column Name]@row, 3) = "Feb", 2, IF(LEFT([Column name]@row, 3) = "Mar", 3, .....................................))))))))))))

  • Bwoods113
    Options

    Thank you so much I understand the formula but I think I may have a sytax error, would you mind looking to see if you can find it? I cant seem to locate it.


    =DATE(VALUE(MID([Last Started]@row, FIND(" ", [Last Started]@row, FIND(" ", [Last Started]@row) + 1) + 1, 4)), =IF(LEFT([Last Started]@row, 3) = "Jan", 1, IF(LEFT([Last Started]@row, 3) = "Feb", 2, IF(LEFT([Last Started]@row, 3) = "Mar", 3, IF(LEFT([Last Started]@row, 3) = "Apr", 4, IF(LEFT([Last Started]@row, 3) = "May", 5, IF(LEFT([Last Started]@row, 3) = "Jun", 6, IF(LEFT([Last Started]@row, 3) = "Jul", 7, IF(LEFT([Last Started]@row, 3) = "Aug", 8, IF(LEFT([Last Started]@row, 3) = "Sep", 9, IF(LEFT([Last Started]@row, 3) = "Oct", 10, IF(LEFT([Last Started]@row, 3) = "Nov", 11, IF(LEFT([Last Started]@row, 3) = "Dec", 12)))))))))))), VALUE(MID([Last Started]@row, FIND(" ", [Last Started]@row) + 1, 2)))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Try removing that equal symbol from before the first IF.

  • Bwoods113
    Options

    Thank you so much that was it, it works perfect. I appreciate your time and knowledge.

    Brandon

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!