Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Formula to calculate the last update of a specific column

Lisa Schick
Lisa Schick ✭✭
edited 12/09/19 in Archived 2016 Posts

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

 

Thanks.

Tags:
· Share on FacebookShare on Twitter

Comments

  • Heidi Decker
    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!

    · Share on FacebookShare on Twitter
  • Lisa Schick
    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.

    · Share on FacebookShare on Twitter
  • Dave B
    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.

    · Share on FacebookShare on Twitter
  • Patrick Galvin
    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

     

    · Share on FacebookShare on Twitter
  • Kate_B
    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. 

    · Share on FacebookShare on Twitter
  • RodC
    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.  

    · Share on FacebookShare on Twitter
  • bruno.benavides
    bruno.benavides ✭✭✭
    edited 01/15/19

    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

     

    · Share on FacebookShare on Twitter
  • brenttopa
    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.

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories