How to convert date text to date in another cell?

cmariscal

I have a field that contains text date and I need to convert it to a date in another cell.  

The format of the text date is Wednesday, February 9, 2022. I need to convert it to a date in a different cell.

Kelly Moore

Hey @cmariscal

With my approach you will need two helper columns - that is, two additional columns that you can shove to the right and hide if desired after the formulas are added in. These are both Text/Number columns. Remember that your final formula will need to go into a DATE formatted column. (Hint: If you temporarily name your column that contains your text string date as "your text-string date column" before you insert my formulas, and the two helper columns as "Month Name" and "Day", then when you change the names back to your actual names all references will self correct in my formulas)

Although you can name these columns whatever you like, I will call them Month Name and Day for clarity. The opportunity in your text string is the month refers to the Month name - which smartsheet doesn't recognize. It was too complicated within the actual formula to do the conversion of the month name to a month number- the nested IF is complicated enough. (If you are interested in creating a separate sheet with just Month Names and Month numbers, we could use that instead). I needed the Day helper column as I was unable to successfully convert the Day to a numeric value within the formula vs a text string that looked like a number. The final DATE formula only worked when the Day was pulled out separately.

In the [Month Name] helper column (Text/Number format)

=MID([your text-string date column]@row, FIND("~", SUBSTITUTE([your text-string date column]@row, CHAR(44), "~", 1)) + 1, FIND("~", SUBSTITUTE([your text-string date column]@row, CHAR(44), "~", 2)) - FIND("~", SUBSTITUTE([your text-string date column]@row, CHAR(44), "~", 1)) - 2 - LEN(VALUE(RIGHT(MID([your text-string date column]@row, FIND("~", SUBSTITUTE([your text-string date column]@row, CHAR(44), "~", 1)) + 1, FIND("~", SUBSTITUTE([your text-string date column]@row, CHAR(44), "~", 2)) - FIND("~", SUBSTITUTE([your text-string date column]@row, CHAR(44), "~", 1))), 2))))

*I know this is an intimidating-looking formula. You should be able to copy paste directly into your Month Name helper column. The CHAR(44) is code for a comma. The formula uses the commas in your text string as anchors to know what part of the text to parse out.

In the Day helper column (Text/Number format)

=MID([your text-string date column]@row, FIND("~", SUBSTITUTE([your text-string date column]@row, CHAR(44), "~", 2)) - 2, 2)

Finally, in your Date column (DATE format)

=DATE(VALUE(RIGHT([your text-string date column]@row, 4)), (IF([Month Name]@row = "January", 1, IF([Month Name]@row = "February", 2, IF([Month Name]@row = "March", 3, IF([Month Name]@row = "April", 4, IF([Month Name]@row = "May", 5, IF([Month Name]@row = "June", 6, IF([Month Name]@row = "July", 7, IF([Month Name]@row = "August", 8, IF([Month Name]@row = "September", 9, IF([Month Name]@row = "October", 10, IF([Month Name]@row = "November", 11, IF([Month Name]@row = "December", 12))))))))))))), VALUE(Day@row))

*After you enter this in your sheet (all these formulas can be converted to column formulas) you can change the column names to whatever you need them to be. All the references will then self correct.

Will this work for you?

Kelly

PS - if you are in a country where semi-colons are used instead of commas, put the formulas first in Word or similar program to convert my commas to semi-colons, THEN, replace CHAR(44) with CHAR(59). CHAR(59) is code for semi-colon.

Kelly Moore

Hey @cmariscal

With my approach you will need two helper columns - that is, two additional columns that you can shove to the right and hide if desired after the formulas are added in. These are both Text/Number columns. Remember that your final formula will need to go into a DATE formatted column. (Hint: If you temporarily name your column that contains your text string date as "your text-string date column" before you insert my formulas, and the two helper columns as "Month Name" and "Day", then when you change the names back to your actual names all references will self correct in my formulas)

Although you can name these columns whatever you like, I will call them Month Name and Day for clarity. The opportunity in your text string is the month refers to the Month name - which smartsheet doesn't recognize. It was too complicated within the actual formula to do the conversion of the month name to a month number- the nested IF is complicated enough. (If you are interested in creating a separate sheet with just Month Names and Month numbers, we could use that instead). I needed the Day helper column as I was unable to successfully convert the Day to a numeric value within the formula vs a text string that looked like a number. The final DATE formula only worked when the Day was pulled out separately.

In the [Month Name] helper column (Text/Number format)

=MID([your text-string date column]@row, FIND("~", SUBSTITUTE([your text-string date column]@row, CHAR(44), "~", 1)) + 1, FIND("~", SUBSTITUTE([your text-string date column]@row, CHAR(44), "~", 2)) - FIND("~", SUBSTITUTE([your text-string date column]@row, CHAR(44), "~", 1)) - 2 - LEN(VALUE(RIGHT(MID([your text-string date column]@row, FIND("~", SUBSTITUTE([your text-string date column]@row, CHAR(44), "~", 1)) + 1, FIND("~", SUBSTITUTE([your text-string date column]@row, CHAR(44), "~", 2)) - FIND("~", SUBSTITUTE([your text-string date column]@row, CHAR(44), "~", 1))), 2))))

*I know this is an intimidating-looking formula. You should be able to copy paste directly into your Month Name helper column. The CHAR(44) is code for a comma. The formula uses the commas in your text string as anchors to know what part of the text to parse out.

In the Day helper column (Text/Number format)

=MID([your text-string date column]@row, FIND("~", SUBSTITUTE([your text-string date column]@row, CHAR(44), "~", 2)) - 2, 2)

Finally, in your Date column (DATE format)

=DATE(VALUE(RIGHT([your text-string date column]@row, 4)), (IF([Month Name]@row = "January", 1, IF([Month Name]@row = "February", 2, IF([Month Name]@row = "March", 3, IF([Month Name]@row = "April", 4, IF([Month Name]@row = "May", 5, IF([Month Name]@row = "June", 6, IF([Month Name]@row = "July", 7, IF([Month Name]@row = "August", 8, IF([Month Name]@row = "September", 9, IF([Month Name]@row = "October", 10, IF([Month Name]@row = "November", 11, IF([Month Name]@row = "December", 12))))))))))))), VALUE(Day@row))

*After you enter this in your sheet (all these formulas can be converted to column formulas) you can change the column names to whatever you need them to be. All the references will then self correct.

Will this work for you?

Kelly

PS - if you are in a country where semi-colons are used instead of commas, put the formulas first in Word or similar program to convert my commas to semi-colons, THEN, replace CHAR(44) with CHAR(59). CHAR(59) is code for semi-colon.

cabbsman

This was very helpful! Thanks

Cleversheet

In case you only need to isolate and "numerize" the month from a textual date, adapt one of the formulas found in this sheet.