Insert date when contents = "Completed"

[Burt_100]

Hi,
I have a worksheet with 2 columns column a "status" and the other column B "date" what I want is whenever the contents in column a change and the entry ="Completed" the date is input into the corresponding cell in column B, I have been using the formula; =IF(A2="Complete",TODAY(),"") however when the day changes this no longer works, Any help would be most appreciated.

[Richard Buttrey]

Hi

A Sheet change macro would probably be best. i.e.

Please Login or Register  to view this content.

End Sub

[Burt_100]

Thanks Richard works well but for some reason it is putting the date in the next cell down in column B instead of the one directly next to it?

[sktneer]

Try this......

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

That suggests that your Exel application is set to move the cursor after entry. Look in the File Options Advanced Editing. Personally I prefer not to have the cursor move whatever I do. I'd rather decide that for myself.

You could either change your settings so that it doesn't move or change the code to

ActiveCell.Cells(0, 2) = Date