Report for files not modified in last 45 days?

Mark Lappegaard

Is it possible to create a report to list all files (sheets) in a folder not modified in the last 45 days? 

To be clear: I know how to run a report that shows the ROWS modified within the sheets.  I want to know when the SHEETS themselves were last modified.  

Background: Currently I have a folder with hundred of sheets.  They are supposed to be accessed monthly but I suspect many are not. Now I sort the folder by "modified" now to see what is out of date.  It's an OK workaround but I'd love a report... 

Thoughts? 

Andrée Starå

Hi Mark,

Interesting!

What do you mean with when the sheets themselves were last modified? Do you want the date when it was last viewed, or it's ok with the last modified date on the sheet?

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

The best suggestion I can make requires some extra work on the front end, but when set will work consistently and can be put into place on new sheets rather easily.

 

Add in a helper column (checkbox will be fine). Use a basic IF statement to check the box next to the most recently modified row.

 

=IF(Modified@row = MAX(Modified:Modified), 1)

 

You can then set up your report to look at the entire workspace and use the following criteria:

1. The box in the helper column is checked.

2. Modified is in the last (days) - 45 then check the box to exclude the selected items.

.

The checkbox ensures you only get one entry per sheet instead of seeing every single row on every single sheet that hasn't been modified in the past 45 days.

 

 

A few things to keep in mind...

 

When you put the column into the sheets, it will show that all rows have been updated on the day you added the column. This essentially resets what will be pulled in your report (you won't see your first results until day 46 after your first sheet is updated with the new column).

 

If there are any TODAY() functions within the sheet, it only needs to be opened and saved then closed out again without any edits actually being made. This means someone could just open the sheet without looking at it, and it won't be pulled in your report.

 

Cell linking and form entries also update the TODAY() function without the sheet even having to be opened, so if either of those two could apply to a sheet, it won't be pulled for your report so long as one of those is updated every 45 days. The sheet wouldn't even have to be opened at all.

Andrée Starå

Paul,

I was thinking the same, but I suspect that Mark wants to know when the sheet was accessed, and I think the API is needed for that.

Nicely done regardless!

Best,

Andrée

Paul Newcome

I re-read the post, and the more I think about it, the more I agree.