possible to have =now() at data entry and not update after that?

[.:mmac:.]

How can I set a cell to contain the =NOW() date only once when data is
entered in the adjacent cells and not have it update after that?
Right now I copy from a cell that contains =now() and paste value only into
the cell. Is there an cleaner way?

 

[Arvi Laanemets]

Hi

From Excel Help (Keyboard shortcuts)
CTRL+; (semicolon) Enter the date
CTRL+SHIFT+: (colon) Enter the time



Arvi Laanemets

 

[.:mmac:.]

Thanks for the reply but thats not what I meant.
I meant that the date would pop in automatically when data was entered in an
adjacent cell.
something like this would be in A1:
= if ((A2 <>0)now(), "")
I'm sure the syntax is wrong but it conveys the idea.

 

[Peo Sjoblom]

See

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

for both a function and a VBA solution

 

[.:mmac:.]

this is the example given, modified for my application; I need the time cell
to be to the left of the entry cell and more cells to return the value.
otherwise it's the same.
But it does nothing and it doesn't show up in the macros list but the
instructions state to put in the "This Workbook" module. perhaps I did that
wrong?

I cant just enter it as a macro because then it puts a wrapper around the
code and says that I need another "End Sub"


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("b3:b1000"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -1).ClearContents
Else
With .Offset(0, -1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

 

[Peo Sjoblom]

Right click the sheet tab (the sheet where you want this to happen) and
select view code, paste it into there, press Alt + Q to close the VBE

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)