Issue with text parsing on date/time populated by automation
![L_123](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
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.
Answers
-
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.
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
-
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.
-
It did not fix the problem. This makes my modified date/time column completely useless.
It's an odd issue
-
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.
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
-
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)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!