Formula to calculate the last update of a specific column

Lisa Schick

Hi. Is there a formula that will calculate the last time data in one specific column was updated?

 

Thanks.

Heidi Decker

Hi Lisa,

There is an Auto-Number/System column type that automatically populates anytime a change is made to a cell in a row but I don't think it can be column specific. It would work if that column is the only column updates are being to.

To set it up, you would add a column with the Auto-Number/System type and select Modified Date. Once you save the sheet, all the dates will populate in that column. Then going forward, any change made to any cell in that row will update with the date.

Maybe it will work for you.

Peace!

Lisa Schick

Currently I am using the System column-Modified Date.  Not exactly what I want since all columns in the sheet are updated but I am only tracking when one specific column is updated.

Dave B

Could be a non-starter, but you could create an alert to email you whenever that column was modified.  Then you could do whatever you need with that info.

Patrick Galvin

any further update to this - is there an option to have SS focus the Auto-Number/System column type on a specific column/field to track updates this way.

so if you have tasks on a sheet that may need multiple people in the team providing updates you can track each on clearly

 

Kate_B

I am having the same issue/need as Lisa and Patrick. Have there been any updates? I would like to track changes to one column only, and pull that out for dashboard reporting. 

RodC

I am having the same need.  I have a drop down menu and I want to know when the last update to the drop down was selected.  I need the from-to of the cell and date it was changed.  I can get this through the activity log info if I download it and use another program report writer to collect the data.  I need something more real time.  Since the data is there I was wondering how can I add a column that calculates/extracts this data.  

bruno.benavides

Hi, I found a way to make that calculation.

When you create a sheet with a Status dropdown menu, you should create a second "tracking" sheet.

In the "tracking" sheet use the first column to "mirror" the column where you have your dropdown menu. Go to each cell of the "mirror" column on the "tracking" sheet and link it to the cell of the corresponding row of the original sheet.

Then, continue working on the "tracking" sheet and set the second column as a Modified(Date) one. It immediately will display the date you created the link to the original sheet on the first column.

Every time you change the dropdown menu cell in the original sheet, the value of the corresponding cell in the "tracking" sheet will also change, and that change will in turn modify the date in the corresponding cell in column 2.

Now, go back to your original sheet. Create a Date column. Go to each cell in that column and link it to the corresponding cell in the Modified(Date) column on your "tracking" sheet. Your original sheet will display the date of the last change in your dropdown menu column for each cell.

Continue on the original sheet and create a Text/number column and write a formula to calculate the difference between today and the date of the last change to the dropdown menu cell.

Example: =TODAY() - ChangeDate2, where ChangeDate is the name of the column that "mirrors" the Modify(Date) column in your "tracking" sheet

 

brenttopa

Hi Bruno, thank you for posting this!  I have been trying to find a way to track tasks completed in the last 30 days by workstream.  I've also incorporated a checkbox column when a task is marked completed. 

 

I started with your suggestion above, then created a report with these parameters:

 

• Checkbox is checked
• Date Difference < 31
• Workstream name selected

 

It seems to be working but obviously every modified date on my mirrored tracking sheet is today.  I will have to retest tomorrow.