excel, automatic date and time when info gets entered

[shorty]

in excel, i am trying to get the date and time to automatically enter itself
when info is entered in another cell

[Celt]

try this formula in the cell where you want the date and time to appear. make sure the cell is formatted for Dates.

=if(cell<>0,NOW(),"")

where cell is the cell reference of where you are entering your data.

[Gord Dibben]

Do you then want that date to be static?

Right-click on the sheet tab and "View Code".

Copy/paste the following event code to that module.

As you enter/edit data in column A, the date/time will be stamped in column B.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
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 = Now
End If
End If
enditall:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

On Wed, 26 Apr 2006 09:09:02 -0700, shorty <[email protected]>
wrote:

>in excel, i am trying to get the date and time to automatically enter itself
>when info is entered in another cell

Gord Dibben MS Excel MVP

[shorty]

this works perfectly except that when I enter something in the cells it makes
the time the same for all the cells. I need every cell to have it's own
actual date and time.


"Celt" wrote:

>
> try this formula in the cell where you want the date and time to appear.
> make sure the cell is formatted for Dates.
>
> =if(-cell-<>0,NOW(),"")
>
> where -cell- is the cell reference of where you are entering your data.
>
>
> --
> Celt
> ------------------------------------------------------------------------
> Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
> View this thread: http://www.excelforum.com/showthread...hreadid=536454
>
>

[Celt]

yeah, my bad. I forgot that both NOW() and TODAY() update themselves when the spreadsheet recalculates. Sorry about that.

Without doing it manually, a macro like Gord suggeted would be the only way to get a static date.