Auto formatting time

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to insert data into one cell, then have the date the data was
inserted, show in another cell automatically. I know I can do this with a
"now" formula, but I don't want this date updated. I want it to always
reference when the original data was input.

Thanks for the help.
 
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
With Target
If .Value <> "" Then
Range("F1").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 the sheet tab and "View Code".

Copy/paste the above into that sheet module.

Type something in A1 and date/time will show in F1.

Alternate code for a range of cells................

The code below will enter a static time in column B whenever a cell in
column A has data input.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col C
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If Excel.Range("A" & n).Value <> "" Then
Excel.Range("B" & n).Value = Format(Now, "dd mm yyyy hh:mm:ss")
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Enter a value in A1 and B1 will return a static date.
Same for A2, A3 etc.


Gord Dibben MS Excel MVP
 
Worked great. You're my hero!

Gord Dibben said:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
With Target
If .Value <> "" Then
Range("F1").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 the sheet tab and "View Code".

Copy/paste the above into that sheet module.

Type something in A1 and date/time will show in F1.

Alternate code for a range of cells................

The code below will enter a static time in column B whenever a cell in
column A has data input.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col C
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If Excel.Range("A" & n).Value <> "" Then
Excel.Range("B" & n).Value = Format(Now, "dd mm yyyy hh:mm:ss")
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Enter a value in A1 and B1 will return a static date.
Same for A2, A3 etc.


Gord Dibben MS Excel MVP
Click to expand...
 
I have a similar question, but I want the date format to be "month YYYY"
(i.e., "August 2009"

How can I change your example to do that?

Thanks,
 
Simply change the Fornat to your choice.

Range("F1").Value = Format(Now, "mmmm yyyy")

Or if you don't care about time

Range("F1").Value = Format(Date, "mmmm yyyy")


Gord
 
Gord,

When I do that, the output is "8/1/2009 0:00"

How can I get it to display the text name of the month?

Thanks,
 
That would be the output in the formula bar, yes?

I get Aug-09 in F1 with line of

Range("F1").Value = Format(Now, "mmmm yyyy")

But I think that depends upon short date settings in Windows OS

Here is revised code which formats F1 to August 2009 no matter what OS date
settings are.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
With Target
If .Value <> "" Then
With Me.Range("F1")
.Value = Date
.NumberFormat = "mmmm yyyy"
End With
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub


Gord
 
Back
Top