Capture the date a sheet was created

delberto

I have a sheet that I create for a review process - new sheet for each review. If the sheet is still open after 2 weeks, I want to do some actions - email the assignee etc.

How do I capture the sheet creation date so I can add some automation? I know I can do this for cell dates but not sure about the actual sheet.

Thanks.

Eric Law

@delberto is there information on this sheet? How would SS know who to email? What do you mean if the sheet is still open? Do you delete sheets as you go?

delberto

Thanks @Eric Law,

The sheet is just a big action list with a tickbox on each row to mark the action as complete. Once all tickboxes are checked, it kicks off an automation to email me that it's complete and then lock all rows. I don't delete the sheets, I view them all from workspace reports that filter sheets by a Complete or In-Progress helper column.

By "still open", I mean that all tickboxes haven't been checked.

The automation for emailing is set up to specifically email me (not a contact in a cell).

So, in this example, if we get to 10 days past the date I created the sheet and all the tickboxes aren't checked - I want to receive an email so I can chase it.

I enter some data in a series of summary sheet fields prior to sending out to the assignee. I could manually enter the creation date here, call it from a helper column then use this date as the x days past creation workflow? I just felt there would be a more elegant way to call the creation date?

Eric Law

@delberto Can you add a Created Date Column for the automation to reference?

delberto

@Eric Law , I could but wouldn't that be the created date for the row?

I think I've missed the point though 🙂 I'll create the sheet from a template - so when I create the sheet, I create all the columns.

I think that's the solution - thanks!

delberto

I'm just coming back to this... @Eric Law , I wonder if you could help again?

As per original post I have a workflow to tell me that a review isn't complete after 2 weeks.

The workflow is triggered every day. The conditions are: A cell in column "Complete" isn't "YES" and the "Auto-Created Date" is more than 2 weeks ago - then it emails me to chase it up.

There are about 20 rows in my sheet. Only the top cell in the "Complete?" is populated but the auto-created date is populated on all rows.

However, I'm getting the emails every day - conditions don't seem to be working. I'm assuming my logic has an error? See picture - any ideas please?

Auto created date is as below - not 2 weeks ago.

Thanks in advance for any help.

Eric Law

@delberto You need to change your first condition to be, is in the last (days). They way you have it is pinging you for everything with no Yes and not in the last days. That means you get things further that that. What I would do is create a 2 weeks from created column, call it 2 Weeks Later and put in the formula =DATEONLY([Auto-Created date]@row +14)

Then change your automation to be

delberto

Thanks @Eric Law . I actually want to be nagged every day once the review is overdue - so I can get it closed. So, using your method, I'm thinking this:

Does that look sensible?

I didn't quite understand why my original "Auto-Created Date is not in the last 10 days doesn't work". Could you explain a bit more please? Thanks again for your help - much appreciated!

Eric Law

@delberto I'm sorry, I got that wrong. Your conditions are good. The only thing I would do is change Completed? into a drop down and update that to be not one of "YES".

So, when you are in the sheet, you have rows that have a "created date" column populated even though there is no other information? Just delete those rows. When playing around in Smartsheet sheets, sometimes there are "rows" at the end of the rows that you are playing in.

delberto

Hi Eric, I create the sheet from a template - so when I create it, all the rows get a created date automatically added.

I have lots of rows - but some only have data in the top cell as they're helper columns.

Understood re the drop down. Thanks.