Hi,
I'm trying to solve a problem...

If I put "x" in A1 I would like for excel to set date
and time in A2 when I put "x", B1 -> B2, ...., and so on.
NOW() does not work because every time I close and open excel it
recalculates.
Is there a formula to set cell date and time without excel refreshing it on
reopen,
without using "manual calculations", and possible without macros and VBA?

Thanks,
MS!

In article <j3b35d$1u9$(E-Mail Removed)>, MySelf.2001 at REMOVEmyself.2001
@gmail.com says...
>
> Hi,
> I'm trying to solve a problem...
>
> If I put "x" in A1 I would like for excel to set date
> and time in A2 when I put "x", B1 -> B2, ...., and so on.
> NOW() does not work because every time I close and open excel it
> recalculates.
> Is there a formula to set cell date and time without excel refreshing it on
> reopen,
> without using "manual calculations", and possible without macros and VBA?
>
> Thanks,
> MS!

<Ctrl>+: sets date

--
Zaidy

Without VBA................John McGimpsey has a method.

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.

Otherwise VBA is your only solution other than a manual entry as Zaidy
suggests...........which does not address your putting an "x" in a
cell.


Gord Dibben Microsoft Excel MVP

On Sat, 27 Aug 2011 17:40:31 +0200, "MySelf.2001"
<(E-Mail Removed)> wrote:

>Hi,
>I'm trying to solve a problem...
>
>If I put "x" in A1 I would like for excel to set date
>and time in A2 when I put "x", B1 -> B2, ...., and so on.
>NOW() does not work because every time I close and open excel it
>recalculates.
>Is there a formula to set cell date and time without excel refreshing it on
>reopen,
>without using "manual calculations", and possible without macros and VBA?
>
>Thanks,
>MS!
>

Thanks to both,
but this is exactly what I was trying to avoid,
manual entry and circular references, beside macros and VBA.
I was hoping for formula that excel won't complain about.

MS!


"Gord" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Without VBA................John McGimpsey has a method.
>
> 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.
>
> Otherwise VBA is your only solution other than a manual entry as Zaidy
> suggests...........which does not address your putting an "x" in a
> cell.
>
>
> Gord Dibben Microsoft Excel MVP
>
> On Sat, 27 Aug 2011 17:40:31 +0200, "MySelf.2001"
> <(E-Mail Removed)> wrote:
>
>>Hi,
>>I'm trying to solve a problem...
>>
>>If I put "x" in A1 I would like for excel to set date
>>and time in A2 when I put "x", B1 -> B2, ...., and so on.
>>NOW() does not work because every time I close and open excel it
>>recalculates.
>>Is there a formula to set cell date and time without excel refreshing it
>>on
>>reopen,
>>without using "manual calculations", and possible without macros and VBA?
>>
>>Thanks,
>>MS!
>>

No such luck.................you have ruled out the only three methods
available to overcome the volatility of the NOW or TODAY functions.

What is your objection to a VBA solution?


Gord

On Sat, 27 Aug 2011 20:09:25 +0200, "MySelf.2001"
<(E-Mail Removed)> wrote:

>Thanks to both,
>but this is exactly what I was trying to avoid,
>manual entry and circular references, beside macros and VBA.
>I was hoping for formula that excel won't complain about.
>
>MS!
>
>
>"Gord" <(E-Mail Removed)> wrote in message
>news:(E-Mail Removed)...
>> Without VBA................John McGimpsey has a method.
>>
>> 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.
>>
>> Otherwise VBA is your only solution other than a manual entry as Zaidy
>> suggests...........which does not address your putting an "x" in a
>> cell.
>>
>>
>> Gord Dibben Microsoft Excel MVP
>>
>> On Sat, 27 Aug 2011 17:40:31 +0200, "MySelf.2001"
>> <(E-Mail Removed)> wrote:
>>
>>>Hi,
>>>I'm trying to solve a problem...
>>>
>>>If I put "x" in A1 I would like for excel to set date
>>>and time in A2 when I put "x", B1 -> B2, ...., and so on.
>>>NOW() does not work because every time I close and open excel it
>>>recalculates.
>>>Is there a formula to set cell date and time without excel refreshing it
>>>on
>>>reopen,
>>>without using "manual calculations", and possible without macros and VBA?
>>>
>>>Thanks,
>>>MS!
>>>
>

Zaidy036 formulated the question :
> In article <j3b35d$1u9$(E-Mail Removed)>, MySelf.2001 at REMOVEmyself.2001
> @gmail.com says...
>>
>> Hi,
>> I'm trying to solve a problem...
>>
>> If I put "x" in A1 I would like for excel to set date
>> and time in A2 when I put "x", B1 -> B2, ...., and so on.
>> NOW() does not work because every time I close and open excel it
>> recalculates.
>> Is there a formula to set cell date and time without excel refreshing it on
>> reopen,
>> without using "manual calculations", and possible without macros and VBA?
>>
>> Thanks,
>> MS!
>
> <Ctrl>+: sets date

Shouldn't that be Ctrl + ;? (Ctrl+: requires using Shift and sets
Time, not date<g>)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

MySelf.2001 wrote on 8/27/2011 :
> Thanks to both,
> but this is exactly what I was trying to avoid,
> manual entry and circular references, beside macros and VBA.
> I was hoping for formula that excel won't complain about.
>
> MS!
>
>
> "Gord" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Without VBA................John McGimpsey has a method.
>>
>> 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.
>>
>> Otherwise VBA is your only solution other than a manual entry as Zaidy
>> suggests...........which does not address your putting an "x" in a
>> cell.
>>
>>
>> Gord Dibben Microsoft Excel MVP
>>
>> On Sat, 27 Aug 2011 17:40:31 +0200, "MySelf.2001"
>> <(E-Mail Removed)> wrote:
>>
>>>Hi,
>>>I'm trying to solve a problem...
>>>
>>>If I put "x" in A1 I would like for excel to set date
>>>and time in A2 when I put "x", B1 -> B2, ...., and so on.
>>>NOW() does not work because every time I close and open excel it
>>>recalculates.
>>>Is there a formula to set cell date and time without excel refreshing it on
>>>reopen,
>>>without using "manual calculations", and possible without macros and VBA?
>>>
>>>Thanks,
>>>MS!
>>>

Why not just enter the date directly using the keyboard shortcut
'Ctrl;'? It would obviate the need to enter 'x' anywhere. (Since you're
willing to do enter something somewhere anyway, why not the date?)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

"GS" <(E-Mail Removed)> wrote:
> Zaidy036 formulated the question :
>> MySelf.2001 at (E-Mail Removed) says...
>>> If I put "x" in A1 I would like for excel to set
>>> date and time in A2
[....]
>> <Ctrl>+: sets date
>
> Shouldn't that be Ctrl + ;?
> (Ctrl+: requires using Shift and sets Time, not date<g>)

Neither one meets the MS's requirement of setting date __and__ time.

I wrote:
> "GS" <(E-Mail Removed)> wrote:
>> Zaidy036 formulated the question :
>>> MySelf.2001 at (E-Mail Removed) says...
>>>> If I put "x" in A1 I would like for excel to set
>>>> date and time in A2
> [....]
>>> <Ctrl>+: sets date
>>
>> Shouldn't that be Ctrl + ;?
>> (Ctrl+: requires using Shift and sets Time, not date<g>)
>
> Neither one meets the MS's requirement of setting date __and__ time.

I meant "neither one __alone__". MS could type ctrl+; followed by
ctrl+shift+:. But I suspect MS would consider that to be a "manual
calculation" <g>, which MS wants to avoid.

joeu2004 formulated on Saturday :
> I wrote:
>> "GS" <(E-Mail Removed)> wrote:
>>> Zaidy036 formulated the question :
>>>> MySelf.2001 at (E-Mail Removed) says...
>>>>> If I put "x" in A1 I would like for excel to set
>>>>> date and time in A2
>> [....]
>>>> <Ctrl>+: sets date
>>>
>>> Shouldn't that be Ctrl + ;?
>>> (Ctrl+: requires using Shift and sets Time, not date<g>)
>>
>> Neither one meets the MS's requirement of setting date __and__ time.
>
> I meant "neither one __alone__". MS could type ctrl+; followed by
> ctrl+shift+:. But I suspect MS would consider that to be a "manual
> calculation" <g>, which MS wants to avoid.

Joe,
According to MS, only the date is required. My post to here is
correcting Zaidy's suggestion to use Ctrl+: (which requires using the
Shift key).

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc