Auto Date Population

[theboatdude]

Good Afternoon, Everyone -

I'm setting up an activity log using Excel. What I'm trying to do i
set it up so that when a person enters his initials in one cell (cel
A1, say), the time and date will automatically populate in the tw
cells to the right(cells B1 and C1). While I'm able to make thi
happen, I'm encountering an interesting problem: Whenever I make an
change to the log (after dates/times have been populated), Exce
automatically updates and makes ALL of the times/dates current (thus
do not wind up with a chronological history - all the cells simple rea
whatever time/date it is now).

The formula I'm using is: =if(A2>0, now( ), " ") . With this,
change the formatting of the time and date columns to only show eithe
time or date, respectively. I tried going into options and turning of
the "automatic calculation" option, but that doesn't work (when I ente
in initials, nothing comes up. When I tell it to calculate (F9)
everthing comes up as the same, current, time)

Is there a way to fix this so that I can get a chrological listing?
Can this even be done?

To answer the obvious question - why don't I simply tell the people t
type the current time/date: a) I want the time and date format to b
consistent (easier to search and chart that way), and b) the peopl
that will be using this are not the most computer literate group, s
I'm trying to make this as easy and "Technician Proof" as I possibl
can.

Thank you

 

[Jason Morin]

NOW() and TODAY() are referred to as volatile functions
and will update anytime a recalc is done. What you need is
a Worksheet Change event that inserts a date and time
stamp when column A is changed (ie someone enters their
initials). Copy the code below:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If .Column <> 1 Then Exit Sub
Application.EnableEvents = False
..Offset(, 1).Value = Format(Now, "mm-dd-yy")
..Offset(, 2).Value = Format(Now, "h:mm:ss")
Application.EnableEvents = True
End With
End Sub

Now right-click on the worksheet tab, View Code, and paste
the code into the window. Close VBE.

HTH
Jason
Atlanta, GA