Converting a text (MONTH YEAR) into a date

Hello,

I am trying to convert a text box that shows the month and year only in a column. So if the cell with the text of “October 2022” is shown, I want a different column to produce “10/01/22”

I would also want to use this date column for automations, so I am curious to know if column formulas could be used as triggers for automations.

To better explain based on the previous example, if October 1st 2022 is the trigger for the automation would it run?

Any insight helps! Thank you!

Best Answers

  • ericncarr
    ericncarr ✭✭✭✭✭
    Answer ✓

    Hi @Adam1234 ,

    You'll need a few columns to accomplish this and a reference table.

    You can build your reference table as hidden in the same sheet or in another sheet to do the conversions from month to number.

    • The month parse column will take your "Month Year" format and extract the name of the month by finding the first 2 and grabbing everything to the left of that (minus the space).
      • =LEFT(Date@row, FIND(2, Date@row) - 2)
    • Your Month Conversion column will take that parsed Month name and look up the corresponding numeric value in your table:
      • =INDEX(COLLECT([Month Numeric]:[Month Numeric], Month:Month, =[Month Parse]@row), 1)
    • Your Year Parse column will grab the year by finding the first "2" and then grabbing all 4 characters of the date:
      • =MID(Date@row, FIND(2, Date@row), 4)
    • Your Finished Date column will then combine all of it together by making a date of the Year Parse and Month Conversion and then "1" for the day.
      • =DATE(VALUE([Year Parse]@row), VALUE([Month Conversion]@row), 1)

    A little bit involved but it'll work until the year 3000 =)

  • ericncarr
    ericncarr ✭✭✭✭✭
    Answer ✓
  • ericncarr
    ericncarr ✭✭✭✭✭
    Answer ✓

    @Adam1234 Hi again, do you mind accepting my answer for this? Thanks!

Answers

  • ericncarr
    ericncarr ✭✭✭✭✭
    Answer ✓

    Hi @Adam1234 ,

    You'll need a few columns to accomplish this and a reference table.

    You can build your reference table as hidden in the same sheet or in another sheet to do the conversions from month to number.

    • The month parse column will take your "Month Year" format and extract the name of the month by finding the first 2 and grabbing everything to the left of that (minus the space).
      • =LEFT(Date@row, FIND(2, Date@row) - 2)
    • Your Month Conversion column will take that parsed Month name and look up the corresponding numeric value in your table:
      • =INDEX(COLLECT([Month Numeric]:[Month Numeric], Month:Month, =[Month Parse]@row), 1)
    • Your Year Parse column will grab the year by finding the first "2" and then grabbing all 4 characters of the date:
      • =MID(Date@row, FIND(2, Date@row), 4)
    • Your Finished Date column will then combine all of it together by making a date of the Year Parse and Month Conversion and then "1" for the day.
      • =DATE(VALUE([Year Parse]@row), VALUE([Month Conversion]@row), 1)

    A little bit involved but it'll work until the year 3000 =)

  • Thanks @ericncarr that worked really well!

  • ericncarr
    ericncarr ✭✭✭✭✭
    Answer ✓
  • ericncarr
    ericncarr ✭✭✭✭✭
    Answer ✓

    @Adam1234 Hi again, do you mind accepting my answer for this? Thanks!

  • Courtney Collier
    Courtney Collier ✭✭✭✭

    @ericncarr I am trying to convert a month received from a drop-down in a form ie "January". I followed your advice above and did a month conversion and a parsed year. I am trying to create the "finished date" column using the formula you provided, however, I receive "incorrect argument.". The formula I am using is below. Can you please assist?

    =DATE(VALUE([Year Parsed]@row, VALUE([Month Conversion]@row), 1)


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Courtney Collier

    The formula above is missing a parenthesis to close off the first VALUE function. Try this

    =DATE(VALUE([Year Parsed]@row), VALUE([Month Conversion]@row), 1)

    Does that work for you?

    Kelly

  • Courtney Collier
    Courtney Collier ✭✭✭✭

    @Kelly Moore Thank you so much! I racked my brain for hours trying to figure out what I was doing wrong. Worked perfectly!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!