+ Start a Discussion

My date keeps recalculating...Please HELP ME!!!

Hi all,
I am trying to get a formula to update a date field when the stage is changed to Won.  The formula I have is (please pity me, I have no knowledge of formulas and am trying my best to get by)...
ISPICKVAL (StageName , "7 - Prepare for Project Execution"),Today()+0 , NULL)
But every time the record it opened it updated to Today again causing inaccurate reports....any suggestions?
The Lone Newfie
Formulas are calculated each time a user views the record, so the behavior you are seeing is correct.  For your use case (setting a fixed date), it seems like Workflow Field Updates might be what you need.  It would be the same formula, just in the workflow field update action.  Workflow is available in Enterprise Edition and above.
Are you using a Workflow rule and then a field update or a validation rule?
Just using the formula in a date field.
What edition do you have?

Create a workflow rule using: Stage equals (your stage here). Your evaluation criteria should be "When a record is created, or when a record is edited and did not previously meet the rule criteria". Go to the next step and select Field Update from immediate actions. Select the date field that should be changed. Select the radion button "Use a formula to set the new value" and in the formula box use Today().

This will update your date field with the date for the day the stage was first at the specified stage. Be sure to use the correct evaluation criteria. I think what is happening with your formula is that it is set to change the date to the current date whenever the record is edited.

...Select the radion button "Use a formula to set the new value" and in the formula box use Today()
                   I meant Radio button here -:smileyvery-happy:

Thanks for all your help.,