Date Modified - Copy of column creates different date

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

Find more posts tagged with

Formulas

Support

Product Development

Cell linking

Enhancement Request

Comments

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.

sm screenshot.png

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.
Join distinct collect with contains
Can someone help with this formula? Its not working as-is, but I also would like to use the "contains"/"has" for the leader column as I combined a bunch of contact columns. Here is my attempt to use has - Heres the references (other sheet) Month, all Leads, and R&B prog is the first one (project)