View Single Post
  #6   Report Post  
Old March 18th 07, 11:44 PM posted to microsoft.public.excel.misc
Gord Dibben Gord Dibben is offline
external usenet poster
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,907
Default NOW() function that is un-changing

CTRL will give you static Date

SHIFT + CTRL + semi-colon will give you a static time.

To get both in the same cell

CTRL + ; <space then CTRL + SHIFT + ;

Otherwise you could use event code to plunk a static date/time in when a cell is

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If Target.Value < "" Then
With Target.Offset(0, 1)
.Value = Format(Now, "dd-mmm-yyyy h:mm:ss")
End With
End If
End If
Application.EnableEvents = True
End Sub

When you enter something in column A the Date/Time is entered in column B.

If you don't want to be able to have the date/time update when editing column A
later make these changes.

If Target.Value < "" _
And Target.Offset(0, 1).Value = "" Then

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module.

Gord Dibben MS Excel MVP

On Sun, 18 Mar 2007 15:45:05 -0700, Cheese

Is there a =NOW() type of function that records the "now" time of when I
first enter it and doesn't update to the current date/time?

I'm trying to create a date/time log of certain events that displays the
date and time in hh:mm:ss - I only need to be accurate within a few seconds,
but the point is that =NOW() wouldn't work.

Any suggestions would be appreciated.