Problem with NOW() function

  • Thread starter Thread starter Geuis
  • Start date Start date
G

Geuis

I am setting up a sheet so that when information is entered into colum
A, when the user tabs over a formula in column B will calculate th
time the data was entered and record it
I am having a problem where every time the B column formula is run, i
recalculates NOW() and changes all previously recorded times to matc
the most recent one.

How can I make it store the time instead of changing every time th
sheet is recalculated?


Formula is:

=IF(A1=0,"",NOW())

Help, I'm stumped
 
You could use a macro something like this

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'will put date in column B when something is put in A
' date will change if new data is entered
'change now to time or date to show time or date
If Target.Count > 1 Then Exit Sub
If Target.Column = 1 Then
Target.Offset(0, 1).Value = Now()
End If
End Sub

To put in this macro right click on the worksheet tab and view code, in the
window that opens paste this code, press Alt and Q to close this window and
go back to your workbook. If you are using excel 2000 or newer you may have
to change the macro security settings to get the macro to run.

--
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 2000 & 97
** remove news from my email address to reply by email **
 
Create a UDF

Function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Create a UDF

Function GetNow()
GetNow = Now
End Function

=IF(A1=0,"",Text(GetNow,"hh:mm:ss"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Or if you want a formula try this =IF(ISBLANK(A1),"",IF(B1="",NOW(),B1))

--
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 2000 & 97
** remove news from my email address to reply by email **
Paul B said:
You could use a macro something like this

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'will put date in column B when something is put in A
' date will change if new data is entered
'change now to time or date to show time or date
If Target.Count > 1 Then Exit Sub
If Target.Column = 1 Then
Target.Offset(0, 1).Value = Now()
End If
End Sub

To put in this macro right click on the worksheet tab and view code, in the
window that opens paste this code, press Alt and Q to close this window and
go back to your workbook. If you are using excel 2000 or newer you may have
to change the macro security settings to get the macro to run.

--
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 2000 & 97
** remove news from my email address to reply by email **
Click to expand...
 
I got it working perfectly. Here's the final code after a few additiona
modifications:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'will put date in column B when something is put in A
' date will change if new data is entered
'change now to time or date to show time or date
If Target.Count > 1 Then Exit Sub

If Target.Column = 1 Then
If Target.Offset(0, 0).Value <> "" Then
Target.Offset(0, 3).Value = Now()

Target.Offset(1, 0).Select

End If

If Target.Offset(0, 3).Value <> "" And Target.Offset(0, 0).Value
"" Then
Target.Offset(0, 3).Value = ""
End If

End If


End Su
 
Geuis, glad you got it working, thought you wanted the time in column B?
This is a little shorter and will do what you want, I think

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'will put date in column B when something is put in A
' date will change if new data is entered
'change now to time or date to show time or date
If Target.Count > 1 Then Exit Sub

If Target.Column = 1 Then
If Target.Offset(0, 0).Value <> "" Then
Target.Offset(0, 3).Value = Now()
End If

If Target.Offset(0, 0).Value = "" Then
Target.Offset(0, 3).Value = ""
End If

End If
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 2000 & 97
** remove news from my email address to reply by email **
 
Back
Top