Static date
 

I have this formula =IF(C170<1,"",IF(C1700,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.

Static date
 

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


"robert morris" wrote in message
...

I have this formula =IF(C170<1,"",IF(C1700,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.

Static date
 

You are missing the fact that NOW() gives current date and time.
If you want a fixed value, use CTRL semi-colon, space, CTRL-shift-colon.
If you want it automatically in response to a value in another cell, you'll
need a VBA solution, which you can find in the archives of this group.
--
David Biddulph

"robert morris" wrote in message
...

I have this formula =IF(C170<1,"",IF(C1700,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.

Static date
 

Hi,

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

it could be simplified as

=IF(C1701,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" wrote:


I have this formula =IF(C170<1,"",IF(C1700,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.

Static date
 

Mike:

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

Bob M.


"Mike H" wrote:

Hi,

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

it could be simplified as

=IF(C1701,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" wrote:


I have this formula =IF(C170<1,"",IF(C1700,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.

Static date
 

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.


"David Biddulph" wrote:

You are missing the fact that NOW() gives current date and time.
If you want a fixed value, use CTRL semi-colon, space, CTRL-shift-colon.
If you want it automatically in response to a value in another cell, you'll
need a VBA solution, which you can find in the archives of this group.
--
David Biddulph

"robert morris" wrote in message
...

I have this formula =IF(C170<1,"",IF(C1700,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.

Static date
 

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" skrev i melding
...
Mike:

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

Bob M.


"Mike H" wrote:

Hi,

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

it could be simplified as

=IF(C1701,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" wrote:


I have this formula =IF(C170<1,"",IF(C1700,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.

Static date
 

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.


"Harald Staff" wrote:

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" skrev i melding
...
Mike:

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

Bob M.


"Mike H" wrote:

Hi,

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

it could be simplified as

=IF(C1701,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" wrote:


I have this formula =IF(C170<1,"",IF(C1700,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.

Static date
 

What I said was "CTRL semi-colon, space, CTRL-shift-colon"
--
David Biddulph

"robert morris" wrote in message
...
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.


"David Biddulph" wrote:

You are missing the fact that NOW() gives current date and time.
If you want a fixed value, use CTRL semi-colon, space, CTRL-shift-colon.
If you want it automatically in response to a value in another cell,
you'll
need a VBA solution, which you can find in the archives of this group.
--
David Biddulph

"robert morris" wrote in message
...

I have this formula =IF(C170<1,"",IF(C1700,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.

Static date
 

David

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

Thanks for the help.

Bob M.


"David Biddulph" wrote:

What I said was "CTRL semi-colon, space, CTRL-shift-colon"
--
David Biddulph

"robert morris" wrote in message
...
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.


"David Biddulph" wrote:

You are missing the fact that NOW() gives current date and time.
If you want a fixed value, use CTRL semi-colon, space, CTRL-shift-colon.
If you want it automatically in response to a value in another cell,
you'll
need a VBA solution, which you can find in the archives of this group.
--
David Biddulph

"robert morris" wrote in message
...

I have this formula =IF(C170<1,"",IF(C1700,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.