NOW() function that is un-changing

[Guest]

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.

 

[Fred Smith]

Ctrl-; will enter the date.
Ctrl-: will enter the time.

If you want both, you have three options.

1. Use =Now(), but after it's entered, Copy it and Paste Special>Values
2. Enter Ctrl-; in one cell, Ctrl-: in another, then add them together.
3. Record a macro which will insert the =Now() value in the selected cell.

 

[Guest]

Thanks for your help.

How would I configure the macro to enter the =NOW() value into a cell rather
than the =NOW() function?

 

[Guest]

Cheese

I don't know if this will work for you but here is a way that I have used
and it works for me.
In a cell out of the way, place your "NOW()" function. Highlite the range
you want to show your date/time log to be. Goto>Data>Validation. Under
"allow" select LIST, under source select the cell that you placed the "NOW()"
function and ok out. Format your range for displaying
date/hours/mimutes/seconds, the way you want.
When you use the drop down to place the date/time it will not update
everytime the sheet re-calculates.

Hope this helps you

Mike Rogers

 

[Guest]

To log into A1, for example:

Sub cheese()
Set r = Range("A1")
r.NumberFormat = "dd-mmm-yyyy hh:mm:ss"
r.Value = Now()
End Sub

 

[Gord Dibben]

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

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

 

[Paul B]

Cheese, like this,

Sub Enter_Now()
ActiveCell = Now

'if you only want to show time
ActiveCell.NumberFormat = "h:mm:ss"
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[CLR]

Sub TimeStamp()
ActiveCell.Value = Time
ActiveCell.EntireColumn.AutoFit
End Sub

Vaya con Dios,
Chuck, CABGx3