Insert a date that does not update if a condition is true

[FirstVette52]

Hey and thanks in advance

I need to insert the current date and time into a cell (that will not update
again) IF another cell is not empty:

IF(C3>0,NOW()," ")

However, this formula allows the date and time to update each time the
workbook functions update. I need the date to be fixed.

Thx!

 

[Shane Devenshire]

Hi,

You will probably have to consider a macro. Do you want to code this?

 

[xlm]

Hi

Try changing the NOW in the formula to TODAY

HTH
--
If this posting was helpful, please click on the Yes button below

Thank You

cheers, francis

 

[FirstVette52]

I am using Vista/ Enterprise 2007

The field holding the formula is a protected field. Thx!

 

[T. Valko]

See this:

http://www.mcgimpsey.com/excel/timestamp.html

--
Biff
Microsoft Excel MVP


"FirstVette52" <(My User name is Firstvette 52, too) firstvet52@(my ISP
E-mail provider is) netzero.com> wrote in message
news:[email protected]...

 

[FirstVette52]

The cell needs to contain a 'Date/Time Stamp', so the TODAY() function is not
an option (always stamps the time as midnight).

Yes please, I would appreciate help with the code. I'm not a VBA Coder but I
understand it somewhat. Thank you both for your help! Greatly appreciated.

 

[Mike H]

Hi,

This works for C3 onlt, if you want a alrger range then post back. Right
click your sheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$C$3" And _
Target.Value > 0 And _
IsNumeric(Target) Then
Application.EnableEvents = False
Target.Offset(, 1).Value = Now
Application.EnableEvents = True
End If
End Sub

Mike

 

[FirstVette52]

The cell holding the Date is C4 (it looks at C3, IF(C3 >0, ...date..., " ")).

This would be for cells in Rows 4, 6, 8, 10, & 12; Columns C thru G, looking
at the cell directly above. -Thx!

 

[Shane Devenshire]

Hi,

Here is some sample code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("C3"))
If Not isect Is Nothing Then
IF [C3]<>"" Then
[A1]=Now
Else
[A1]=""
End If
End Sub

An FYI: in formulas where you want to display blank use "" not " ".



If this helps, please click the Yes button.

"FirstVette52" <(My User name is Firstvette 52, too) firstvet52@(my ISP
E-mail provider is) netzero.com> wrote in message
news:[email protected]...