I am working on list of tasks for my team. And met with couple of troubles.
I need to track date of completion and after that its approval. Unfortunately when I have tried to create "completion date" with this formula:

=IF( [Task Status]="Completed"
           YEAR([Modified on])
           ;MONTH([Modified on])
           ;DAY([Modified on])

or another one

    [Task Status]="Completed"
    ;[Modified on]

the date will change anytime when someone made any change. Even when approver (manager) approves the task. It automatically changes the completion date to last modifying.
I am getting lost. I've tried InfoPath, do scan of billions chats in internet but without any clever solution. Could someone help with that? How to lock/fixed "completion date" when owner of task change the status to "Completed"?
I am intermediate user of InfoPath, beginner of SP Designer/workflows but loser in any coding (Java, C#,...).
Appreciate any help. Many thanks.


Formulas are re-calculated on every Item change

So the only way to get a fixed Date, is to use a Workflow that writes a date to a [Modified On] (Date)Field, and only if that Date field is empty
You can not use Formulas for this.


  • Is [Modified On] Empty ?
    • Is [Tasks Status] Completed ?
      • Then Set [Modified On] to Today()
  • Nice, workflow sounds better to me. Just was nor sure if it is possible.
    – Adriana
    Mar 29 '17 at 12:55
  • May I please ask for some manual or detailed description of that workflow?
    – Adriana
    Mar 29 '17 at 12:56
  • Build this with a SharePoint Designer Workflow Mar 29 '17 at 12:57
  • Many thanks Danny for your help. But I dont know how to create it :( Would you think you will be able to put screen of that workflow from Sp Designer? Realy appreciate it.
    – Adriana
    Mar 29 '17 at 14:27
  • You are trying to run before you can walk. Start by learning SharePoint Workflows. Here is a one hour YouTube course: youtube.com/watch?v=ccfBQQRTWFk Mar 29 '17 at 15:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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