none
Update date if a specific column is changed RRS feed

  • Question

  • Hi there.

    I'm working on a SharePoint project online (2010 I believe) and it has a column that asks you to select the status of the project we're tracking. There is a drop down box of 5 options.

    I would like there to be a column that shows when this status field was last updated.

    I've already made a column that is made when the issue is raised automatically and cannot be changed, but I would like a second column to show when the status was last changed.

    I realise I can use =Modified, but that will update if the name is changed, or the person to whom it's assigned. I don't want that, just when the status drop down box was changed from whatever it's currently on, to a new option. eg, "New" - "Issue"

    Thanks in advance.

    Chris

    Friday, October 18, 2019 12:50 PM

Answers

  • Hi,

    I don’t think it is achievable using [Modified] in calculated column since every modification to the item will change the value of Modified column.

    As far as I know, there is no OOB solution to identify when a specific field is changed. You will need custom solutions to get the modified time of a specific column.

    Here’s another post with similar request for your reference.

    Calculated field - Get Modify Date when a Particular Column is get Changed.

    Please note: Microsoft provides third-party contact information to help you find technical support. This contact information may change without notice. Microsoft does not guarantee the accuracy of this third-party contact information.

    Best regards,

    Chelsea Wu


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Wednesday, October 23, 2019 8:22 AM

All replies

  • Hi Christopher,

    You have couple of option to add the date value in your second column,

    1) SharePoint Workflow (https://sharepointmaven.com/6-ways-to-create-workflows-in-sharepoint/)

    2) Calculated column - IF(Column1 = "Option",[today],"")

    _______________________ 


    Thivagar SEGAR
    [If this Post helps you, then please don't forget to mark it answer or Vote for this post]

    Friday, October 18, 2019 2:43 PM
  • Hi Thivagar. 

    I was attempting to run this as a calculated column (I don't have a clue about workflow) But couldn't seem to make it work.

    Could you explain what the "option" part in this formula does?

    Thanks

    Friday, October 18, 2019 3:00 PM
  • Hi Christopher,

    We cannot add [today] or [Me] function inside the calculated column. Please see the below screenshot:

    For calculated column for this we have just have only "Modified" column but this column will be updated whenever any update happens to the list. So we cannot use this column in this scenario. 

    To achieve the same we can use any of the below approaches:

    1. Update the second column for example  "Project status last modified" using the designer workflow. 
    2. Update the second column for example  "Project status last modified" using the Nintex workflow.
    3. Update the second column for example  "Project status last modified" using the MS flow.
    4. Update the second column for example  "Project status last modified" using the event receiver code(if you are working in SharePoint on-premise).

    Note:

    In the above workflow approach need to use the "Set field value" or "Update field value" action and start the workflow conditionally i.e. if any changes happens in status column then only trigger the workflow. 


    Thanks&Regards,

    Habibur Rahaman

    MCSA,MCP,MCTS

    My Certificates

    Note: If you feel the proposed answer is helpful, please mark as so or if this answered your question,please mark as answer.


    Friday, October 18, 2019 4:31 PM
  • Hi,

    I'm checking how it is going on with this issue.

    Please remember to update this thread if you have any progress.

    Thank you for your understanding.

    Best regards,

    Chelsea Wu


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Monday, October 21, 2019 7:50 AM
  • wouldn't 'project status last modified' change the date whenever any changes are made to the project (such as name or comment? There are several columns in this grid, but I only want the one saying status date to edit when the status is updated.

    I realise calling the column "status" does not help clarification in this circumstance

    Monday, October 21, 2019 8:11 AM
  • Hi Christopher,

    Apologize for not providing the statement clearly, based on your requirement you can frame the calculated column formula like below one:

    IF([StatusColumn] = "New",[Modified],"") - Single Option - this one when the user select New option, that time it will capture Modified date

    IF(OR([StatusCoumn]="New",[StatusCoumn]="Issue"),"[Modified]","") - Multiple option - this one when user select "New" or "Issue" that time it will capture modified date, you can add some more option also in this formula.

     Just consider i created new column called "status updated" with calculated column data type and entering the above given formula.

    Here StatusColumn is where user selecting the option from drop down

    and "New" is one of the option from your drop down box

    so when the user selecting the option "New", calculated column will capture the current modified date to "Status Updated" column.

    _______________________ 


    Thivagar SEGAR
    [If this Post helps you, then please don't forget to mark it answer or Vote for this post]

    Monday, October 21, 2019 9:46 AM
  • Hi Christopher,

    Apologize for not providing the statement clearly, based on your requirement you can frame the calculated column formula like below one:

    IF([StatusColumn] = "New",[Modified],"") - Single Option - this one when the user select New option, that time it will capture Modified date

    IF(OR([StatusCoumn]="New",[StatusCoumn]="Issue"),"[Modified]","") - Multiple option - this one when user select "New" or "Issue" that time it will capture modified date, you can add some more option also in this formula.

     Just consider i created new column called "status updated" with calculated column data type and entering the above given formula.

    Here StatusColumn is where user selecting the option from drop down

    and "New" is one of the option from your drop down box

    so when the user selecting the option "New", calculated column will capture the current modified date to "Status Updated" column.

    _______________________ 


    Thivagar SEGAR
    [If this Post helps you, then please don't forget to mark it answer or Vote for this post]

    Really useful, and I think I'm super close to getting the answer now, but when I run this line, it just returns the "status updated column" with [modified]. Ideally I wanted it to pull the date down. 

    Would I need to make a hidden column called "modified" with the current date? so then it will pull that across? Or am I missing something really simple here?

    Monday, October 21, 2019 10:55 AM
  • Wait, I think I've got it.

    I made a new column called "today", that simple took todays date.

    I then made the formula

    =IF(OR(Status="New",Status="Issue"),Today,"")

    I then deleted the column "today" and it seems to keep working!

    Thank you all so much. As its only updated by the day, I cant check it until tomorrow, but I believe this is a result!

    =2 karma points for all

    Monday, October 21, 2019 11:04 AM
  • Hi Christopher,

    Better use [Modified] instead of Today, because [Today] will capture current date, so if you edit that list item tomorrow and saved it, the value get changed to current date. 

    So if you use [Modified], then it will capture the last modified date and it will stay forever. 

    Note: if anyone again choose "New" or "issue" status, then formula will re-capture the modified date.

    _______________________ 


    Thivagar SEGAR
    [If this Post helps you, then please don't forget to mark it answer or Vote for this post]

    Monday, October 21, 2019 11:45 AM
  • thats kind of what I wanted, I think.

    If the status is changed from new to issue, I want the date to be updated to that date.

    But if a comment is added, that shouldn't update the date right?

    As comments sometimes need to be added whilst a project is still in the issue state, I didn't want the overall status date to update every day, only when that status column is edited. 


    Monday, October 21, 2019 12:09 PM
  • I tried modified and today, but it still updated the column status date when i changed a comment. 

    Ideally, I wanted it so that it would only update the status updaed column when the option for status was changed.

    Is this even possible?

    Tuesday, October 22, 2019 7:54 AM
  • Hi,

    I don’t think it is achievable using [Modified] in calculated column since every modification to the item will change the value of Modified column.

    As far as I know, there is no OOB solution to identify when a specific field is changed. You will need custom solutions to get the modified time of a specific column.

    Here’s another post with similar request for your reference.

    Calculated field - Get Modify Date when a Particular Column is get Changed.

    Please note: Microsoft provides third-party contact information to help you find technical support. This contact information may change without notice. Microsoft does not guarantee the accuracy of this third-party contact information.

    Best regards,

    Chelsea Wu


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Wednesday, October 23, 2019 8:22 AM
  • Hi, 

    Thanks for marking my reply as the answer to this thread. I’m glad to know the information is helpful to you.

    Here I will provide a summary of this post for your information.

    [Update date if a specific column is changed - Summary]

    Expectation 
    ====================
    Update date field when a specific column is modified

    Environment
    ====================
    SharePoint 2010

    Solution
    ====================
    This request is not achievable to OOB solution since we cannot identify when a specific column of an item is changed. 

    Custom solutions such as jQuery code are required to get the modified time of a specific column.

    References
    ====================
    https://sharepoint.stackexchange.com/questions/23154/calculated-field-get-modify-date-when-a-particular-column-is-get-changed

    Please note:
    Microsoft provides third-party contact information to help you find technical support. This contact information may change without notice. Microsoft does not guarantee the accuracy of this third-party contact information.

    Best regards,

    Chelsea Wu


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Thursday, October 24, 2019 1:41 AM