IF/AND

Khari Shiver

Hello,

There may be a more efficient way to solve this so please feel free to suggest an alternate way. I have a column in my sheet that collects the time/date a form is submitted using the "Created - Date" field option . I need to set up an automation that would be triggered by the time the form was received AND the day of the week. I'm using the WEEKDAY() function for the latter part. It's the former I'm having trouble with.

I added an additional column and used the MID() function to extract the time from the column with the time/date stamp.

Submission Time/Date column: 05/13/21 10:31 PM

Column29: 10:31

For this text example, I want the time period that triggers the alert to be between 2:30 and 10:30. Below is what I've written as a column formula:

=IF(AND([Column29]@row > "2:30", [Column29]@row < "10:30"), "Send Alert", "Do Not Send Alert")

In every cell I've gotten a false/"Do Not Send Alert" but there are values between those times.

Capture.JPG

I think it has something to do with the data types but I'm not sure how to resolve the issue. The Submission Time/Date column is formatted as Auto-Number/System Created(Date) while Column29 is Text/Number. I don't know if there's a way to cast one column as another data type as you can in SQL. Any help would be greatly appreciated.

Thank you,

Mark Cronk

Hi Khari,

Smartsheet views your times as text so < and > don't work. You need to extract. You can use SUBSTITUTE to replace the : with . and the use VALUE() to make sure it's viewed as a number.

In Column 29 you use VALUE(SUBSTITUTE([insert your formula to derive the time], ":","."))

In your alert flag column use numeric criteria - no quotes.

Work?

Mark

Mark Cronk

Hi Khari,

Smartsheet views your times as text so < and > don't work. You need to extract. You can use SUBSTITUTE to replace the : with . and the use VALUE() to make sure it's viewed as a number.

In Column 29 you use VALUE(SUBSTITUTE([insert your formula to derive the time], ":","."))

In your alert flag column use numeric criteria - no quotes.

Work?

Mark

Khari Shiver

Hey Mark!

Your approach got me 95% of the way there. I ran into an issue where if the time did not have 5 places i.e. "10:00" vs "9:00" the formula would give an #invalid data type" presumably because there was no ":" to substitute. I adjusted my formula to only capture the first two positions i.e. "10" or "9" and it works.

Once again, THANK YOU for your help!

Khari

Mark Cronk

Hi Khari, Excellent. Glad you were able to figure it out. Thank you for contributing to the Community.

Mark