Copy contents of text cell to a date cell same sheet

fransl_6310

Good Day,

I am creating a new row with Power Automate. As I understand it, I can only populate text columns with Power Automate?

The one column I need to update is a Date Column. I created a 2nd text column which I populate with the date info from Power Automate.

How can I update the original Date column with the 2nd columns text on the same sheet?

I am new to SS, so will appreciate any help or ideas.

Many thanks

Toufong Vang

Hi, @fransl_6310

Creating a Smartsheet date value from a set of string values will require the DATE() and VALUE() functions. For example, if the year is "2022", the month is "9", and the day is "23", then the expression to create a date value from the strings will be DATE( VALUE("2022") , VALUE("9") , VALUE("23") ).

Assuming that your Power Automate date string is in the format "9/23/2022", then one approach would be...

1) Create 3 helper columns, _YEAR_, _MONTH_, _DAY_

2) Use the formula, =RIGHT([PA_String]@row, 4) , to return the part of the string that is the year.

3) Use =LEFT([PA_String]@row, FIND("/", [PA_String]@row) - 1) to return the month.

4) The day is everything after the first "/" once you have subtracted the year "/2022" from the date. To do this...

• FIND("/", [PA_String]@row) returns the position of the first "/" in the date string.

• LEN([PA_String]@row) -5 returns the length of the date string minus the year and the second "/".

• The number of characters that comprise the day is LEN([PA_String]@row) -5 - FIND("/", [PA_String]@row) .

• Use the MID() function to extract the portion of the date string that comprises the day...

MID([PA_String]@row, FIND("/", [PA_String]@row) + 1, (LEN([PA_String]@row) - 5) - (FIND("/", [PA_String]@row))

5) Use the DATE() function to convert the _YEAR_, _MONTH_, _DAY_ string values into a Smartsheet date value.

=DATE(VALUE([_YEAR_]@row), VALUE([_MONTH_]@row), VALUE([_DAY_]@row))

If you don't want to use helper columns, then combine the expressions together. The resulting formula is

=DATE(VALUE(RIGHT([PA_String]@row, 4)), VALUE(LEFT([PA_String]@row, FIND("/", [PA_String]@row) - 1)), VALUE(MID([PA_String]@row, FIND("/", [PA_String]@row) + 1, (LEN([PA_String]@row) - 5) - (FIND("/", [PA_String]@row)))))

fransl_6310

Thanks @Toufong Vang

I will give this a go and let you know.

Regards

Chris Shifflett

Worked really well for me! Thanks for this solution!