Date/Time Stamp Option

Paul Newcome

While trying to figure something else out, I stumbled across a new option for having specific actions date/time stamped using the stem generated Modified (date) type column and the new Copy Row automation.

.

DISCLAIMER:

This does not scale very well as is. If you have a lot of rows you are wanting to track a lot of changes on, it can get very cumbersome very quickly.

My personal use for this solution is tracking a one-time change on each row (think "Completed" box gets checked).

.

Here's how it works...

I have the system generated Modified (date) column. I have another set of columns that separates the date from the time for further calculations, but you could even use a single extra column that simply duplicates the Modified column.

Set up the automation to trigger according to what you want to capture the date/time stamp for.

When a row is copied, only the data is copied. Not the formula. So whatever data is in the extra column that is formula driven to duplicate the Modified column will be pulled, but the formula to duplicate won't. It will also pull attachments and comments as well.

This effectively captures the date/time stamp for a specific action.

.

Yes. The date/time stamp is on a different sheet, but that just means that you can restrict access to the historical data.

Yes. It doesn't scale very well, but at least it is a step in the right direction and gives us an option to date/time stamp natively within SS.

Mike Wilday

@Paul That is a genius use case for Copy rows! 

Andrée Starå

Hi Paul,

Nicely done! 

I've used Move Row similarly previously, but this will work better for many other processes.

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

 

Mike Wilday

I've seen ideation work by smartsheet for applying modified timestamps to specific columns/feilds. I really, really, REALLY, REALLY, 

____ ____  ____                _       _

|      | |         |       |    |     |      \      /

|___| |___   |__ _|    |     |        \  /

|     \  |____ |       |    |__ |__      | 

can't wait for that feature. 

Andrée Starå

I approve of this message!

Paul Newcome

Thanks Mike. My example above is actually a VERY simplified version of how I am actually using it.

 

My actual use case is a rather complex setup across 3 sheets.

 

Sheet 1 has a listing of weekly availability on an automatically rolling schedule that updates to show only availability AFTER date/time of most recent update on any of the three sheets. That was tricky, but I ended up collecting data from the most recently modified row on each sheet, turning it into a stamp of yyyymmddhhmm, then cell linking across the sheets.

 

This allowed me to convert dates/times of availability into the same format and do comparisons to keep everything up to date. 

 

Sheet 1 also has a listing of 27 different skill sets, open tasks/tickets, and total tasks/tickets (open and total are coming from sheet 3).

 

This is for a group of 6 people.

.

Sheet 2 is my original help request/assignment sheet. Someone submits a form (think help desk ticketing) that describes an issue and can select multiple areas (which is where I got the skillsets from on the first sheet).

 

Then I built out a scoring system that compares the user's submission to each person's skillsets.

Then it compares it to who is next available.

Then it looks at how many tickets each person has open.

Then it looks at how many total tickets each person has handled.

Once it accounts for ALL of that... It will automatically assign the "best fit" based on all of that criteria to the ticket.

 

The challenge that the Copy Row automation resolved was how to lock in that data. Sheet 1 is ever changing, so I don't want tickets on sheet 2 constantly getting reassigned. I also wanted to be able to capture why that person was considered the "best fit" for various reasons.

 

Which led me to sheet 3...

.

Sheet 2 has an automation set up to copy a row as soon as the "Best Fit" column changes from blank to any value.

 

That row gets copied to sheet 3 where those values are locked in. This is the actual working sheet. It also speeds things up a bit because even though there is still a lot of data on this sheet, very little of it contains actual formulas. So when my people are updating tickets and whatnot, they have a faster sheet to work in, and are less likely to be able to jack up formulas since they don't have access to the Assignment Sheet (Sheet 2).

.

And that is how the Copy Row feature has helped me build a Help Desk Ticketing system with auto-assignment of tickets in Smarsheet. The need for this came as my company started to adopt the SS platform across multiple departments. Not everyone has licenses for various reasons, and of those that do have licenses, not everyone is very proficient.

 

So we pulled together 6 people who set aside an hour each week specifically for SS to answer questions and help out the other folks. Because of the very quick expansion of SS throughout our processes, we decided to come up with this pretty little mess, and it seems to be working pretty well (so far).

Mike Wilday

Allison H

@Paul Newcome , how did you separate out the date and time from the modified date column?

Thanks!

Allison

Paul Newcome

To pull just the date, you would use

=DATEONLY(Created@row)

To pull only the time, you would use

=SUBSTITUTE(Created@row, DATEONLY(Created@row), "")

Katye Reed

This was super helpful! Thanks Paul!

Paul Newcome

@Katye Reed Happy to help. 👍️

Mike Wilday

You can now set a timestamped date based on another cell change using an automation workflow!

https://help.smartsheet.com/articles/2481986-set-the-current-date-with-record-a-date-action?_ga=2.79950226.443393604.1609776795-1074882870.1602114816

Paul Newcome

@Mike Wilday Yes! And it has made a lot of processes so much simpler. I also heard baselining (even on Gantt) is on the way in 2021. Now we just need some time calculations...

Ella

@Paul Newcome I am testing with your method. It worked yesterday, but today I am getting weird times. Not sure why it keeps switching between EST and PST from the automation itself.

No idea what broke. When the date is captured, I extract the time from Modified.

My Modified time is 1.05pm, the time that gets extracted in my helper column is 10.05am? and that gets recorded as my time stamp.

Mommaduck

@Mike Wilday and @Paul Newcome I was hoping to find something like this - but I'm not what I'd call a superuser so hoping I'm doing something wrong. We have a sheet to track visitor time spent at a resource center. Back in 2020, Paul, you kindly wrote time calculation formulas for me for which I have been eternally grateful!! But now folks want to simplify - and goof proof - time/date entries. Time spent often spans 2 shifts of employees. Shift 1 Intake staff enters (by form) persons name, etc and I've created a time stamp auto generated column for day and time to capture arrival date and time. Shift 2 would use an automated line entry request to enter day and time leaving (and occasionally that spans more than 1 day). We'd like to have it auto-generated as well. I tried date modified but the problem with that was when staff reviewed data entry, if they had to correct spelling of the visitor's name -sadly, frequently - then that changed modified time - skewing the data we need of time spent. I tried your record date automation - but it's only the date. By the end of each month (we report monthly) there can be 700-800 lines each representing one visit so it didn't sound like your copy row was probably a good option. None of our company super users had any other ideas and since you seemed to know time, I thought I'd ask! Thank you for at least reading this!! Joanne

Mike Wilday

I am not your time guy! :D Hopefully @Paul Newcome will have something to input! Yeah, the modified column setting is tricky.