date in excel

[MySelf.2001]

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]

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

 

[Gord]

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

 

[MySelf.2001]

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]

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

 

[GS]

Zaidy036 formulated the question :

<Ctrl>+: sets date

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

 

[GS]

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!

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?)

 

[joeu2004]

Zaidy036 formulated the question :

MySelf.2001 at (e-mail address 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.

 

[joeu2004]

Zaidy036 formulated the question :

MySelf.2001 at (e-mail address 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.

 

[GS]

joeu2004 formulated on Saturday :

Zaidy036 formulated the question :
MySelf.2001 at (e-mail address 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).

 

[GS]

Sorry, you are correct about MS needing both date and time.

 

[GS]

MySelf.2001 formulated on Saturday :

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!

Select the cells to receive the current date/time.

Put this macro in a standard module:

Sub InsertDateAndTime()
Selection.Value = Now()
End Sub

In the macros dialog, select the macro and assign a keyboard shortcut
to it in the 'Options' dialog.

**Note that you could put this in PERSONAL.XLS so it can be used
anytime you want it. This would obviate any need for macros/VBA to be
in the project workbook.

HTH

 

[Frederic LE GUEN - MVP Excel]

MySelf.2001 formulated on Saturday :




Select the cells to receive the current date/time.

Put this macro in a standard module:

  Sub InsertDateAndTime()
    Selection.Value = Now()
  End Sub

In the macros dialog, select the macro and assign a keyboard shortcut
to it in the 'Options' dialog.

**Note that you could put this in PERSONAL.XLS so it can be used
anytime you want it. This would obviate any need for macros/VBA to be
in the project workbook.

HTH

--
Garry

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

Noooo !
Function is better than Sub.
Function InsertDateAndTime()
InsertDateAndTime = Date & " " & Time
End Function
So if you write in any cell =InsertDateAndTime() you return the
current date and time

 

[GS]

Frederic LE GUEN - MVP Excel laid this down on his screen :

Noooo !
Function is better than Sub.
Function InsertDateAndTime()
InsertDateAndTime = Date & " " & Time
End Function
So if you write in any cell =InsertDateAndTime() you return the
current date and time

And what prevents that formula from recalcing every time the wkb is
opened?

 

[GS]

It happens that GS formulated :

Frederic LE GUEN - MVP Excel laid this down on his screen :

And what prevents that formula from recalcing every time the wkb is opened?

I meant to ask how this function works without declaring a return data
type?

 

[Rick Rothstein]

Function is better than Sub.

And what prevents that formula from recalcing every time the
wkb is opened?

The function does not use Application.Volatile, so it is not volatile. Since
there are no cell references in the function call, the function won't be
forced to recalculate when any of the recalculation methods are activated.

I meant to ask how this function works without declaring a
return data type?

Like variables, functions default to Variant when no data type is specified.

Rick Rothstein (MVP - Excel)

 

[GS]

Rick Rothstein wrote :

The function does not use Application.Volatile, so it is not volatile. Since
there are no cell references in the function call, the function won't be
forced to recalculate when any of the recalculation methods are activated.


Like variables, functions default to Variant when no data type is specified.

Rick Rothstein (MVP - Excel)

Thanks, Rick! That's very helpful to know...