Problem with formula IF(C5="", "",NOW())
 

I use IF(C5="", "",NOW()) to reflect the date of entry but it changes every
time with new day..how to prevent it?
in other words, if i entered smth yesterday in C5 so that for example in c4
it shows 15/02/05 so I want C4 be the same tomorrow as well, not change C4 to
tomorrow's date!

You can do it with event code

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A5:Z5")) Is Nothing Then
With Target
.Offset(-1, 0).Value = Now
.Offset(-1, 0).NumberFormat = "dd mmm yyyy hh:mm"
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ruslan" wrote in message
...
I use IF(C5="", "",NOW()) to reflect the date of entry but it changes
every
time with new day..how to prevent it?
in other words, if i entered smth yesterday in C5 so that for example in
c4
it shows 15/02/05 so I want C4 be the same tomorrow as well, not change C4
to
tomorrow's date!

Dear Bob,
it did not work.
Let me explain it more precise.

I need to have in A1:A100 cells the date of entry in cells C1:C100
or in other words, if i enter anything today for example in cell C50 smth
then I have to have 15/02/2005 in cell A50. And that date must remain
unchanged tomorow as well


"Bob Phillips" wrote:

You can do it with event code

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A5:Z5")) Is Nothing Then
With Target
.Offset(-1, 0).Value = Now
.Offset(-1, 0).NumberFormat = "dd mmm yyyy hh:mm"
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ruslan" wrote in message
...
I use IF(C5="", "",NOW()) to reflect the date of entry but it changes
every
time with new day..how to prevent it?
in other words, if i entered smth yesterday in C5 so that for example in
c4
it shows 15/02/05 so I want C4 be the same tomorrow as well, not change C4
to
tomorrow's date!

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("C1:C1000")) Is Nothing Then
With Target
.Offset(0,-2).Value = Date
.Offset(0,-2).NumberFormat = "dd/mm/yyyy"
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ruslan" wrote in message
...
Dear Bob,
it did not work.
Let me explain it more precise.

I need to have in A1:A100 cells the date of entry in cells C1:C100
or in other words, if i enter anything today for example in cell C50 smth
then I have to have 15/02/2005 in cell A50. And that date must remain
unchanged tomorow as well


"Bob Phillips" wrote:

You can do it with event code

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A5:Z5")) Is Nothing Then
With Target
.Offset(-1, 0).Value = Now
.Offset(-1, 0).NumberFormat = "dd mmm yyyy hh:mm"
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ruslan" wrote in message
...
I use IF(C5="", "",NOW()) to reflect the date of entry but it changes
every
time with new day..how to prevent it?
in other words, if i entered smth yesterday in C5 so that for example
in
c4
it shows 15/02/05 so I want C4 be the same tomorrow as well, not
change C4
to
tomorrow's date!