If cell in column contains "ASAP", change value of cell or match another cell's value

KTM

Hi Smartsheet Community!

Issue we are trying to resolve:

We have an intake request form that provides the client with an opportunity to request a preferred start and end date range or fill in with "ASAP". However, when the client enters in "ASAP" it causes the Requested Work Week column to have an error, which causes the column to expand. Even if we wrap text, it will make the row expand and then the cell alignment can be a little funky.

What we think could resolve the issue:

We want to create a formula where any cell within that Requested Start Date Range column that contains "ASAP" automatically converts to the date the request was submitted - using the Date Submitted Column. If the cell does not contain "ASAP" then we want it to remain as the date the client requested. Below is the screenshot of the columns next to each other.

If that formula is not possible, then we would like to have "ASAP" change to "!” or anything that won’t cause the cell to expand.

I am inexperienced with formulas but here is what I've tried to do in the Requested Start Date Range cell with "ASAP" in it.

=IF(CONTAINS("ASAP", [Request Start Date Range]@row], MATCH([Date Submitted]@row)))

^ I receive #UNPARSEABLE error

We’re also open to any other suggestions as far as making the cell not expand with the #invalid error. 

Thank you!

Nic Larsen

See if this gets you closer:

=IF([Requested Start Date Range]@row = "ASAP", [Date Submitted]@row, [Requested Start Date Range]@row)

or this one - wasn't too clear on what you wanted based on image above

=IF([Requested Start Date Range]@row = "ASAP", [Date Submitted]@row, [Date Submitted]@row)

Nic Larsen

See if this gets you closer:

=IF([Requested Start Date Range]@row = "ASAP", [Date Submitted]@row, [Requested Start Date Range]@row)

or this one - wasn't too clear on what you wanted based on image above

=IF([Requested Start Date Range]@row = "ASAP", [Date Submitted]@row, [Date Submitted]@row)

KTM

@Nic Larsen

The second formula you provided worked when I entered it into a newly created column. Then I can have the Requested Work Week formula work off of that newly created column. Thanks so much for your response and your help!