Issue with text parsing on date/time populated by automation

L_123

I'm having an issue where I have a created date column, then a date column that referenced the created date column and uses text parsing to remove the time. formula below:

=DATE(VALUE(20 + MID([Issue Opened Date]@row, 7, 2)), VALUE(LEFT([Issue Opened Date]@row, 2)), VALUE(MID([Issue Opened Date]@row, 4, 2)))

This functions well enough, but then I added an "alert when anything changes" automation on the spreadsheet. Now i'm getting alerts saying the date column is being updated on every cell when someone enters and saves the sheet, even without changes. I have bypassed this by monitoring specific columns for changes now, so I have a workaround, but I don't think it should function like this.

Jason Tarpinian

Try using the DATEONLY() function instead of the formula you've built. But if your "Issue Opened Date" is the created date column, I don't know that that would solve your automation issue. As the created date will never be changing.

L_123

It is the created column, I wasn't aware of the dateonly() function, thanks. I've switched to that, and will see if it corrects.

L_123

It did not fix the problem. This makes my modified date/time column completely useless.

It's an odd issue

Jason Tarpinian

The only other thing I can think of then is there is a column formula that is auto-calculating each time you open the sheet. I can't think of any specific examples of which functions do that right now (probably TODAY()), but if you open a sheet, before you even do anything, watch your formula columns to see if they flash and then your save button becomes active.

If that's what's happening, you could then just update your trigger to all columns but those culprits. So depending on how many columns you've got, that might be a lot.

L_123

Yeah, that's how I've got it running right now, but it makes my modified date/time column always just be the current date/time, it becomes fairly useless. I want to see how long it's been since items were touched in an easy way (not via action or cell history)