I want to see todays date in cell A1 if I type any thing in cell B1, and I
want that date to stay same until I delete the value in cell B1

That would not be Conditional Formatting

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 A1, enter this in
A1:

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

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

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


Gord Dibben MS Excel MVP


On Tue, 24 Jun 2008 16:58:01 -0700, Kimti <(E-Mail Removed)>
wrote:

>I want to see todays date in cell A1 if I type any thing in cell B1, and I
>want that date to stay same until I delete the value in cell B1

Thank you very much for your help. It worked.

"Gord Dibben" wrote:

> That would not be Conditional Formatting
>
> 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 A1, enter this in
> A1:
>
> =IF(B1="","",IF(A1="",NOW(),A1))
>
> Format A1 as you wish to display date, time, or both. If B1 is initially blank,
> A1 will return a null string (""). When a value is entered into B1, A1 will
> evaluate as "", therefore NOW() will be returned. After that (as long as B1
> remains populated), A1 will evaluate to a date/time and therefore will return
> the value in A1 - i.e., the date/time.
>
> ...................................................
>
>
> Gord Dibben MS Excel MVP
>
>
> On Tue, 24 Jun 2008 16:58:01 -0700, Kimti <(E-Mail Removed)>
> wrote:
>
> >I want to see todays date in cell A1 if I type any thing in cell B1, and I
> >want that date to stay same until I delete the value in cell B1
>
>

Thanks for the feedback and thanks to John.


Gord

On Tue, 24 Jun 2008 17:41:00 -0700, Kimti <(E-Mail Removed)>
wrote:

>Thank you very much for your help. It worked.
>
>"Gord Dibben" wrote:
>
>> That would not be Conditional Formatting
>>
>> 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 A1, enter this in
>> A1:
>>
>> =IF(B1="","",IF(A1="",NOW(),A1))
>>
>> Format A1 as you wish to display date, time, or both. If B1 is initially blank,
>> A1 will return a null string (""). When a value is entered into B1, A1 will
>> evaluate as "", therefore NOW() will be returned. After that (as long as B1
>> remains populated), A1 will evaluate to a date/time and therefore will return
>> the value in A1 - i.e., the date/time.
>>
>> ...................................................
>>
>>
>> Gord Dibben MS Excel MVP
>>
>>
>> On Tue, 24 Jun 2008 16:58:01 -0700, Kimti <(E-Mail Removed)>
>> wrote:
>>
>> >I want to see todays date in cell A1 if I type any thing in cell B1, and I
>> >want that date to stay same until I delete the value in cell B1
>>
>>