I've set up a spreadsheet to enter the current time and date in particular
cells when data is entered into another cell (basically i'm using a formula
to record the time data was entered into each cell).

I've used the following formula to do so, =IF(C20,NOW( )),
Problem is the cell just updates when the file is saved as the formula gets
recalculated.

I believe i need to find a way of allowing induvidual cells to calculate
only once to prevent this from happening.

Anyone have any ideas? or know of another way to accomplish this task?

You need to remove the NOW() function once it has served its usefulness.
Either copy the cell containing the NOW() formula and paste it as value back
onto itself (the manual way) or use an Event Macro to detect C2 changing and
have the Macro write the time/date stamp.
--
Gary's Student


"Marc G" wrote:

I've set up a spreadsheet to enter the current time and date in particular
cells when data is entered into another cell (basically i'm using a formula
to record the time data was entered into each cell).

I've used the following formula to do so, =IF(C20,NOW( )),
Problem is the cell just updates when the file is saved as the formula gets
recalculated.

I believe i need to find a way of allowing induvidual cells to calculate
only once to prevent this from happening.

Anyone have any ideas? or know of another way to accomplish this task?

As an alternative, you may want to look at how JE McGimpsey does it based on a
cell changing:
http://www.mcgimpsey.com/excel/timestamp.html

Marc G wrote:

I've set up a spreadsheet to enter the current time and date in particular
cells when data is entered into another cell (basically i'm using a formula
to record the time data was entered into each cell).

I've used the following formula to do so, =IF(C20,NOW( )),
Problem is the cell just updates when the file is saved as the formula gets
recalculated.

I believe i need to find a way of allowing induvidual cells to calculate
only once to prevent this from happening.

Anyone have any ideas? or know of another way to accomplish this task?

--

Dave Peterson

Private Sub Worksheet_Change(ByVal Target As Range)
You can use this macro to add the time and date stamp and it will
automatically update the time & date. This macro assumes that the filed that
you are updating is in column A and puts the time & date stamp in column C.

Dim cc As String
If Target.Column = "1" Then
cc = Target.Row
'Add formula to cell with time and date
Range("C" & cc).Select
ActiveCell.FormulaR1C1 = "=IF(R[-1]C[-2]0,NOW())"
Range("C" & cc).Select
'Copy and paste time and date
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Else
End If
End Sub

"Marc G" wrote:

I've set up a spreadsheet to enter the current time and date in particular
cells when data is entered into another cell (basically i'm using a formula
to record the time data was entered into each cell).

I've used the following formula to do so, =IF(C20,NOW( )),
Problem is the cell just updates when the file is saved as the formula gets
recalculated.

I believe i need to find a way of allowing induvidual cells to calculate
only once to prevent this from happening.

Anyone have any ideas? or know of another way to accomplish this task?