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.