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

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

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

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
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

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.


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=536454