Keeping date from changing

R

Richard

I'm having a problem trying to get this formula to work
=IF(C4=0,NOW(),IF(C4>0,"",IF(E4="",NOW(),E4))). The date keeps changing each
day. Is formula works fine =IF(B3="","",IF(C3="",NOW(),C3)).
Tools/options/calculations/Iterations. Can anyone please help me with the
first formula. Thanks in advance!
 
E

Earl Kiosterud

Richard,

As it happens, the date does change each day. Thus NOW() returns the current date. If you
want something to look at those cells and plop in a fixed date, it'll take a macro.
 
G

Gary''s Student

This is an example using cell A1. In A1 we have:

=IF(C4=0,NOW(),IF(C4>0,"",IF(E4="",NOW(),E4)))

The following worksheet event macro waits for A1 to become "visible":

Private Sub Worksheet_Calculate()

Set a1 = Range("A1")
t = a1.Value
nw = "NOW()"

rp = "date(" & Year(Now()) & "," & Month(Now()) & "," & Day(Now()) & ")"
If Len(t) = 0 Then Exit Sub
f = a1.Formula
If InStr(f, nw) = 0 Then Exit Sub
Application.EnableEvents = False
a1.Formula = Replace(f, nw, rp)
Application.EnableEvents = True
End Sub

The macro sees that the formula contains NOW() and changes it into:

=IF(C4=0,DATE(2008,4,26),IF(C4>0,"",IF(E4="",DATE(2008,4,26),E4)))

The macro is smart enough to use the correct values in DATE(). The macro is
also smart enough to "fix" the formula only one time.


Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm
 
R

Richard

Thanks for you're help Earl but there is a way to do this without writing a
macro because this formula works =IF(B3="","",IF(C3="",NOW(),C3)).
Tools/options/calculations/iteration. And the date will not change . I just
can't figure out how to rewrite this formula do get the same results.
=IF(C4=0,NOW(),IF(C4>0,"",IF(E4="",NOW(),E4))).
 
G

Gord Dibben

Richard

From John McGimpsey...................

Using circular references and worksheet functions
You can use a circular reference to enter the time when a change is made in
another cell, then maintain that time. Choose Tools/Options/Calculation
(Preferences/Calculation for Macs) and check the Iteration checkbox. Then, if
your target cell is A1 and you want the date/time to appear in B1, enter this in
B1:

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

Format B1 as you wish to display date, time, or both. If A1 is initially blank,
B1 will return a null string (""). When a value is entered into A1, B1 will
evaluate as "", therefore NOW() will be returned. After that (as long as A1
remains populated), B1 will evaluate to a date/time and therefore will return
the value in B1 - i.e., the date/time.

...................................................


Gord Dibben MS Excel MVP
 
E

ed

Richard

From John McGimpsey...................

Using circular references and worksheet functions
You can use a circular reference to enter the time when a change is made in
another cell, then maintain that time. Choose Tools/Options/Calculation
(Preferences/Calculation for Macs) and check the Iteration checkbox. Then,if
your target cell is A1 and you want the date/time to appear in B1, enter this in
B1:

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

Format B1 as you wish to display date, time, or both. If A1 is initially blank,
B1 will return a null string (""). When a value is entered into A1, B1 will
evaluate as "", therefore NOW() will be returned. After that (as long as A1
remains populated), B1 will evaluate to a date/time and therefore will return
the value in B1 - i.e., the date/time.

..................................................

Gord Dibben  MS Excel MVP




- Show quoted text -
Click to expand...

Your date changes in both formulas because NOW() changes every day.
You can lock it in by F-2 and F-9. Then that cell will show the
"Locked-in" date rather NOW(). I don't know how to do that
automatically as I have been trying to for YEARS.

ed
 
Top