Using RIGHT/LEFT or FIND/REPLACE?

Jimrny

I thought that by using RIGHT and returning the values after ", " that it would fix the issue I'm having but depending on the difference in timestamps, I may get 7 or 8 characters so this formula doesn't work out for me.

Would using FIND/REPLACE work, such as finding ", " within the column and replacing that with just "" so it would just leave me with the Time value?

This all stems from using the following formula to extract just the time on another sheet.

=SUBSTITUTE([Completed Timestamp]@row, DATEONLY([Completed Timestamp]@row), ""

When used with MOVE/Automation with a check box value being true, it populates correctly. However when using MOVE/Automation when rows are added via form submission and has a matching value in a Category column, it populates with a comma in front on the End Time value.

Example: ", 2:41 PM"

I figured I could just create a helper column to remove the comma and space that populates as a prefix before the time value.

Any help on a proper formula for this?

Toufong Vang

In the column Closed, place the formula,

=IF( FIND(",",[End Time]@row), RIGHT([End Time]@row, 7), [End Time]@row)

When the first character in [End Time]@row is "," FIND() will return "1" (which a Boolean value of TRUE).

Mike TV

@Jimrny

Why not just use:

=SUBSTITUTE([End Time]@row, ", ", "")

Jimrny

https://community.smartsheet.com/discussion/comment/331344#Comment_331344

It still gives me only 7 characters, once the time end time is XX:XX PM versus X:XX PM, it will only give me the 7 characters.

Jimrny

https://community.smartsheet.com/discussion/comment/331345#Comment_331345

Eureka! Didn't even think about that! Thank you