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

On Sat, 26 Apr 2008 09:29:00 -0700, Richard
wrote:


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(C40,"",IF(E4="",NOW(),E4))).

"Earl Kiosterud" wrote:

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.
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Richard" wrote in message
...
I'm having a problem trying to get this formula to work
=IF(C4=0,NOW(),IF(C40,"",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!