6

Hello, 

I am working to create an automation off of the date modified field. This is to alert users if they have not updated a row in more than 4 days. Since the date modified field is not recognized as a date field I created a copy of the column using the basic formula: 

=[Last Modfied]1

My results are not what I would expect. Take a look at the screen shot. 10/31 is showing at 11/1 - From my testing I am guessing the cut off is any time after 4PM the date becomes a day later? Is there a way to update this to 12AM? or is this an bug that smartsheets can fix?  

Thanks for taking a look, 

EPayne

 

 

Comments

Hi,

 

If all that you want is to notify users of no updates after 4 days, why don't you just create an automation that does that, and save yourself the hassle of having another column?

In reply to by Jose da Silva

I agree, that would be the most obvious way to do it. However, you can not create an automation off of the date modified column because it is not recognized as date column by the tool. 

Hence the extra column with the odd error. I am open to other solutions. 

Andree_Stara

Hi,

Try something like this and see if it works better.

=DATEONLY([Last Modified]@row)

Did it work?

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

work-bold

I was excited about that idea but the error persists. 

 

 

Andree_Stara

In reply to by EPayne

Where are you located? There have been some issues before, depending on the region. The issue in your case might be something else.

I'll get back to you if I think of something else, but in the meanwhile, I'd recommend If you haven’t already, that you reach out to the Smartsheet Support Team. Smartsheet Support Team

 

In reply to by Andrée Starå

Thanks Andree, I am in Reno NV.

https://community.smartsheet.com/discussion/time-zone-clarification-needed

I did find this :

"System columns in Smartsheet capture date/timestamps in UTC but display the value in the timezone set in your Personal Settings. When using a function like DATEONLY(), Smartsheet is going to pull in the captured value (in UTC) which may not reflect the date in the your timezone."

 

they provided a possible solution: 

DATEONLY() function, i.e. =DATE(YEAR(Modified1), MONTH(Modified1), VALUE(LEFT(Modified1, 2)))​

however, for 11/14/2019 I get 11/11/2019, the value portion is referencing the month instead of the day but I think I could play around with this idea and parse it out into 3 columns and concatenate it back together correctly. 

it should work, kind of a complicated fix but a solution none the less. 

If you are able to fix the timezone in the UTC or have it pull from a updated timezone table, I would appreciate it!

Thank you!