How to freeze NOW()

  • Thread starter Thread starter gopher87
  • Start date Start date
G

gopher87

Hi all,


I am using the NOW() to avoid typing in the date/time of some data i am
collecting.


the problem is that all the cells with now get recalculated every time i
enter new data in the spreadsheet.

How do I "freeze" the older data so that NOW() does not get recalculated
in those cells or is there a way to transform the output of now into
some text so that it does not get recalculated?.

thanks
 
Rather than =NOW(), type CTRL+; (semi-colon) followed by space and then
CTRL+: (colon)
to get date and time as if you had typed them
best wishes
 
Instead of NOW() worksheet function or CTRL + SHIFT + ; you could use some
event code to enter a static Now in adjacent when you type in a cell.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
With Target
If .Value <> "" Then
.Offset(0, 1).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the above into
that module.

Edit the range to suit then Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP
 
Bonsour® gopher87 avec ferveur ;o))) vous nous disiez :
I am using the NOW() to avoid typing in the date/time of some data i
am collecting.

the problem is that all the cells with now get recalculated every
time i enter new data in the spreadsheet.

How do I "freeze" the older data so that NOW() does not get
recalculated in those cells or is there a way to transform the output
of now into some text so that it does not get recalculated?.
Click to expand...

select the cell
click on the formula within the formula bar
hit F9 key

HTH
 
Back
Top