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.

This should do what you want

http://www.mcgimpsey.com/excel/timestamp.html


--
Regards,

Peo Sjoblom



"GARDNERGUY" wrote in message
...
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

On Tue, 17 Apr 2007 08:46:00 -0700, GARDNERGUY
wrote:

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.

Worked great. You're my hero!

"Gord Dibben" wrote:

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

On Tue, 17 Apr 2007 08:46:00 -0700, GARDNERGUY
wrote:

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.

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,



"Gord Dibben" wrote:

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

On Tue, 17 Apr 2007 08:46:00 -0700, GARDNERGUY
wrote:

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.

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

On Tue, 25 Aug 2009 09:01:01 -0700, Demosthenes
wrote:

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,



"Gord Dibben" wrote:

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

On Tue, 17 Apr 2007 08:46:00 -0700, GARDNERGUY
wrote:

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.

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,



"Gord Dibben" wrote:

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

On Tue, 25 Aug 2009 09:01:01 -0700, Demosthenes
wrote:

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,



"Gord Dibben" wrote:

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

On Tue, 17 Apr 2007 08:46:00 -0700, GARDNERGUY
wrote:

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.

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

On Tue, 25 Aug 2009 13:59:03 -0700, Demosthenes
wrote:

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,



"Gord Dibben" wrote:

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

On Tue, 25 Aug 2009 09:01:01 -0700, Demosthenes
wrote:

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,



"Gord Dibben" wrote:

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

On Tue, 17 Apr 2007 08:46:00 -0700, GARDNERGUY
wrote:

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.