Static date

[robert morris]

I have this formula =IF(C170<1,"",IF(C170>0,NOW(),0)) in Col B which is
triggered by entering a number i.e., 125 in Col C.

My problem is all previous Rows in Col C change along with the single entry
in the next Row down.

I need the previous dates and times to remain as they were.

What am I missing?

Bob M.

 

[Harald Staff]

Hi Bob

NOW() will always return "now" meaning last time of sheet recalculation. If
you need static entries then you must either type them manually into a cell,
or have a macro type them for you. Formulas can't do these things.

HTH. Best wishes Harald

 

[Mike H]

Hi,

First off I don't understand your formula
=IF(C170<1,"",IF(C170>0,NOW(),0))

it could be simplified as

=IF(C170>1,NOW(),"")

That said, you cant insert a static date with a formula but you could
utilise the worksheet change event. Right click your sheet tab, view code and
paste the code below in. change the range to suit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("c1:c170")) Is Nothing Then
If IsNumeric(Target) Then
Application.EnableEvents = False
If Target.Value > 1 Then Target.Offset(, -1).Value = Now()
Application.EnableEvents = True
On Error GoTo 0
End If
End If
End Sub

Mike

 

[robert morris]

Mike:

I removed my formula, copied your VB code in Col C. The same problem exists.
Did I follow your instructions correctly?

Bob M.

 

[robert morris]

David;

I use this little form to monitor my blood sugar therefore the time of day
is somewhat important. Ctrl ; does not return the time. I have formatted
the cell for both date & time. Nothing seems to work.

Bob M.

 

[Harald Staff]

Bob

VB code goes in VB modules, not in worksheet cells.
Rightclick the shet tab, choose "View code", paste the code in the white
module sheet that appears, return to the Excel sheet and test.

HTH. Best wishes Harald

 

[robert morris]

Harald:

My poor choice of words. Yes, I put the VB code in the module. It still
changes all dates to "NOW" I thought as I moved down to a new Row(s) the
possibility existed it could leave the previous rows as they were when
entered.

Thanks for the help.

Bob M.

 

[robert morris]

David

Stupid is as stupid does! I'm sorry, I failed to read your post carefully.

Thanks for the help.

Bob M.