Using RIGHT/LEFT or FIND/REPLACE?

Jimrny · 05/27/22

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 · 05/27/22

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 · 05/27/22

@Jimrny

Why not just use:

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

Jimrny · 05/27/22

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 · 05/27/22

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

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

Using RIGHT/LEFT or FIND/REPLACE?

Answers

Help Article Resources

Categories