none
Calculated Column: Prevent date from changing - SharePoint 2013 online RRS feed

  • Question

  • Hi Everyone,

    Need some help please - currently working on SharePoint 2013 online.

    I have a choice column (drop down) called 'Stage'

    In 'Stage', I have 3 choices;

    1. Start
    2. Pause
    3. End

    I also have 3 calculated columns (formatted for Date only);

    1. Start
    2. Pause
    3. End

    What I am trying to do is, if 'Start' is selected in 'Stage' drop down, it applies current days date in 'Start' calculated column. If you select 'Pause', then it applies current days date in 'Paused' calculated column etc.

    I have got it to work using the following in each calculated column (e.g. below is from the 'Start' calculated column);

    =IF(Stage="Start",TODAY(),)

    What is happening though is, when I change from 'Start' to 'Pause' in 'Stage' it is deleting the previous entry. E.g. Start = 19/06/2020 but when changed to 'Pause', 'Pause' = 19/06/2020 but 'Start'=0. I want the previous entry to retain it's date (think of it like an audit trail).

    Can anyone advise what I am doing wrong? Admittedly, I am only a week old in SharePoint so I am certain it's me that is the problem here. Would really appreciate any help, been on this for 3 days now and can't find anything online.

    Thank you!



    Friday, June 19, 2020 4:38 AM

Answers

  • Hi Jayson,

    What you're experiencing is expected behavior.

    The IF function returns the current date when the condition is true, and the default maximum date (or 0) when the condition is false.

    Depending on your needs, I recommend that you create a custom workflow (via SharePoint designer):

    1.Create three date column: start pause end

    2.Create the custom workflow:

    3.Change the workflow setting:

    4.Result:

    Best regards

    Itch Sun


    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, June 22, 2020 8:31 AM

All replies

  • Hi Jayson,

    What you're experiencing is expected behavior.

    The IF function returns the current date when the condition is true, and the default maximum date (or 0) when the condition is false.

    Depending on your needs, I recommend that you create a custom workflow (via SharePoint designer):

    1.Create three date column: start pause end

    2.Create the custom workflow:

    3.Change the workflow setting:

    4.Result:

    Best regards

    Itch Sun


    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, June 22, 2020 8:31 AM
  • Hi 

    Would you please provide us with an update on the status of your issue?

    Best regards

    Itch Sun


    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, June 29, 2020 6:37 AM
  • Hi,

    This thread has been around for a long time. Please remember to mark the replies as answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

    Thank you for understanding! If you have any questions, or updates, please feel free to let me know.

    Best Regards,

    Itch Sun


    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, July 1, 2020 1:26 AM
  • Hi Itch,

    Apologies something came up in my personal life that kept me away for a while. I don't have access to Sharepoint Designer however I was successful in replicating your suggestion above in Nintex Workflow which solved the issue.

    Thank you

    Jayson

    Thursday, July 2, 2020 12:11 AM