Date

[jcrownshaw]

I am trying to get one cell to return todays date (static) when another cell is marked as "closed" so that I have a running record of when items are closed. Can you help?

 

[Glory]

In cell b1:

IF ($A$1="closed", TEXT( TODAY() ,"mm/dd/yyyy") ,"")

Edit:

No, I see what you're saying. You might need to use more than one cell for that if you're going to use formulas... or VBA.

 

[Glory]

This assumes the date will be adjacent and to the left of the "closed" string entry.

In the worksheet object module:

Option Compare Text
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Target.Value = "Closed" Then Target.Offset(, -1).Value = Date
 
End Sub

 

[jcrownshaw]

Great Thank you!! How do I access the worksheet module to input the code? Thanks so much again!

 

[Glory]

Click "Tools -> Macro -> Visual Basic Editor". Click "View -> Project Explorer". Double-click the node that has your sheet name on it. Then paste it in. You might want to make a copy first in case something goes wrong.