Converting a text (MONTH YEAR) into a date

Adam1234

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!

ericncarr

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

@Adam1234 you're welcome!

ericncarr

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

ericncarr

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 =)

Adam1234

Thanks @ericncarr that worked really well!

ericncarr

@Adam1234 you're welcome!

ericncarr

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

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

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

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

Jason F

Is there a way to set it where it would always be the last date of the month as opposed to the first?