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

Question

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!

Answer 1

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.

Answer 2

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.

Answer 3

the respond is: you may not insert a photograph right into a cellular! you may: a million. Insert a photograph and make the row top and column width bigger, so it seems as though the photograph is in the cellular. 2. Use the icons that image fonts could desire to upload some low-tech spark into your worksheet.