2

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;

  • Start
  • Pause
  • End

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

  • Start
  • Pause
  • 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!

Improve this question
7
  • This is the known behavior in SharePoint. calculated column values gets updated whenever an item is updated in list. To achieve what you are trying you may need to use SP designer workflow/Power automate flow.
    – Ganesh Sanap - MVP
    Commented Jun 19, 2020 at 5:18
  • ok that's good to know, so no way of achieving this via calculated formula.
    – Jayson
    Commented Jun 19, 2020 at 5:20
  • Calculated column values always get update whenever an item is change, it will not retain the previous value. As you are trying to achieve this in SharePoint 2013 , SharePoint Designer or custom JavaScript could be an option you can use .
    – Diksha Poddar
    Commented Jun 19, 2020 at 5:21
  • Thank you @DikshaPoddar - could you by chance direct me to content that could assist me with this in Java? I am not familiar with it at this stage.
    – Jayson
    Commented Jun 19, 2020 at 5:24
  • Do you have access to Script Editor or Content Editor webpart on your site? Are you good at coding in JavaScript (not java)?
    – Ganesh Sanap - MVP
    Commented Jun 19, 2020 at 5:42

2 Answers 2

Reset to default
1

You can not achieve this using Calculated column.

One option to this is SharePoint Designer as explain below -

  1. Create 3 date columns each for Start Date, Pause Date and End Date.

  2. Create a SharePoint Designer workflow that will trigger when an item is created and when an item is modified.

  1. Write below steps in workflow to compare Stage value and accordingly set the respective Date column value to today's date.

my

Improve this answer
6
  • You can also add one more condition inside each IF block like--> If Start Date is empty then Set Start Date to Today. This is to handle the cases when your Stage is Start and item is modified because of some other reasons (still Stage is Start). In that case Start Date should not set again and it should retain its previous value.
    – Ganesh Sanap - MVP
    Commented Jun 19, 2020 at 6:48
  • This is awesome but I don't have SharePoint Designer I do seem to have access to Nintex Workflows but no idea how to use it. Does SharePoint online have it's own workflow that I could build your instructions in? When I click Sharepoint Designer it takes me to download page (my company then blocks it). Really appreciate your help Diksha
    – Jayson
    Commented Jun 19, 2020 at 6:49
  • If you have access to Nintex then you can also achieve the same using Nintex workflows.
    – Ganesh Sanap - MVP
    Commented Jun 19, 2020 at 6:52
  • You're welcome . Please upvote and accept it as answer if it helped you.
    – Diksha Poddar
    Commented Jun 19, 2020 at 6:54
  • Nintex workflows also works in a similar way as SharePoint Designer workflow. You can try it or you can use Power Automate as well which will be having different steps but the logic will be the same.
    – Diksha Poddar
    Commented Jun 19, 2020 at 6:57
0

This is the known behavior in SharePoint.

Calculated column values gets updated whenever an item is changed/updated in list and it will not retain its old values.

To achieve what you are trying you may need to use SharePoint designer workflow/Power automate flow. Using these you can set the date column values based on the choice column("Stage") values.

References:

  1. How To Set A Date Value In A SharePoint Designer Workflow

  2. Microsoft Flow: Update SharePoint Columns

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.