Inserting date from formuls

  • Thread starter Thread starter TechGuy
  • Start date Start date
T

TechGuy

Hi all,

I have a data verification drop down created and I am trying to make the
date poplate in certain cells depending on my drop down choice. The main
thing I have found for this would be the "today()" value. The problem with
this though is that the date changes daily. I need this to be a static value.
Any help would be great. Thanks.

=IF(K8="closed", TODAY())
 
Make use of an Event Macro in that sheet level:
----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
[L8] = ""
If [K8] = "closed" Then [L8] = Date
Application.EnableEvents = True
End Sub
 
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("K1:K20")) Is Nothing Then
With Target
If .Value = "closed" Then
.Offset(0, 1).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on sheet tab and "View Code"

Copy/paste the code into that sheet module.

Edit range to suit. Alt + q to return to Excel.


Gord Dibben MS Excel MVP
 
Back
Top