If/then <insert date> question - MS Excel?

Hi there. I have table with the following:





________________________


|Task Status | Date Completed|


~~~~~~~~~~|~~~~~~~~~~~~~|


Completed | 10.29.2007 |


In Progress | |


Completed | 10.22.2007 |





and the following formula:





=IF(E5=$E$1,(NOW()),"")





What I want to do is if the Task Status column (E5) has the word "COMPLETED" in it, then today's date will appear in the cell to the right. If any other word or character is inserted, the cell will remain blank. This part works fine.





The problem is that if I change the system date to another day, then enter a 'Completed' status on E6 for instance, for all other cells that have a 'Completed' entry, these dates now change to today's date.





Not sure if I explained that well. Anyway, is there a way to use the IF function, insert a date, and have it stick?





Appreciate the help, thanks!

If/then %26lt;insert date%26gt; question - MS Excel?
Unfortunately, the formula will be re-evaluated often, and NOW will never again be the date you want. I can't think of any way of automatically freezing the field the first time it's evaluated.





Sorry.
Reply:There is a Yahoo! group called the Excel Experts Club. I used to be a member and it isn't just for experts. I'm sure that they will help you as this is the type of question they thrive on.





Hope this helps.