Date Modified - Copy of column creates different date

EPayne

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

 

 

Jose da Silva

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?

EPayne

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. 

Andrée Starå

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

EPayne

I was excited about that idea but the error persists. 

 

 

Andrée Starå

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

 

EPayne

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!

 

 

Andrée Starå

Happy to help!

I'll get back to you if I come to think of anything!

Also, let me know if you think of anything!