Question about IF(AND function

Mike.Brown

I am trying to capture the modified date when a date is entered. Even though the modified date will change anytime the row is changed, I am trying to capture it once based on the entry of a date. I have the timestamp field looking to see if the date has been entered, if so it pulls the modified date time over. That didn't work, because anytime the row was modified either by a person or an automation, the modified date would change and get pulled over into the repones timestamp.

So I added a helper checkbox column in the hopes if that checkbox was checked, the timestamp wouldn't change and it would all work. The problem is that when I check the box, it wipes out the timestamp. Currently if you enter a date, it pulls the modified date over, but when I check the box, it wipes the timestamp out instead of leaving it there.

Here is my IF statement. =IF(AND(NOT([R Time]@row), NOT(ISBLANK([1ST ATTEMPT DATE]@row))), [Most recent change]@row + "", "").

Paul Newcome

Happy to help. 👍️

Paul Newcome

Here is a method I developed for capturing a date/time stamp for a specific action. Once you get this second sheet and automation plugged in, you can use a formula with a cross sheet reference such as INDEX/MATCH or INDEX/COLLECT to pull the static value from the second sheet back over to the first sheet:

https://community.smartsheet.com/discussion/63561/date-time-stamp-option

Mike.Brown

I thought about doing it that way but it is a will be a fairly large sheet that would be duplicated solely for the purpose of capturing a timestamp based on an action. It's too bad that Smartsheet doesn't do time formulas better. One of the columns in the new sheet will be the modified date, so won't I have the same problem in that sheet?

Paul Newcome

No. The method above outlines how to capture that Modified column as static data. It is the static piece you pull back over.

Mike.Brown

I tried to use the

=SUBSTITUTE(Created@row, DATEONLY(Created@row), "") in the pag that I am pulling the static time from and get an INVALID DATE TYPE error. The column is Text/Number? Any ideas why?

Paul Newcome

Because the DATEONLY function is designed for the system generated modified date/time or created date/time columns. Why are you trying to isolate the time?

Mike.Brown

We are trying to get down to the hour on response time. Most of the calls are returned the same day so we wanted to be able to track the response time down to the hour if possible.

Paul Newcome

You are going to want to incorporate a LEFT function instead then.

=SUBSTITUTE(Created@row, LEFT(Created@row, FIND(" ", Created@row)), "")

Mike.Brown

Can you help me with the INDEX/MATCH formula for pulling the static modified date back into the original sheet

Paul Newcome

You would need some type of unique identifier on every row. Then your formula would look something like this:

=INDEX({Static Field To Pull}, MATCH([Unique ID]@row, {Unique ID}, 0))

Mike.Brown

Awesome. Thanks!

Paul Newcome

Happy to help. 👍️