Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Difference between NOW() and TODAY()

317 views
Skip to first unread message

Michael.Tarnowski

unread,
Feb 10, 2009, 10:58:59 AM2/10/09
to
Hi community,

in excel help I found the information

TODAY():
"Returns the serial number of the current date. The serial number is
the date-time code used by Microsoft Excel for date and time
calculations. If the cell format was General before the function was
entered, the result is formatted as a date."


NOW():
"Returns the serial number of the current date and time. If the cell
format was General before the function was entered, the result is
formatted as a date.
...
The NOW function changes only when the worksheet is calculated or when
a macro that contains the function is run. It is not updated
continuously. "

I have a cell formatted as TT. MMM JJ, hh:mm ;;

when I copy-n-paste a cell showing NOW() I get the current time but an
older date; when I copy-n-paste a cell showing today() I get the
current date but the time 00:00.
I want to have after copy-n-pasting the current date and the current
time. Do I have to force a re-calculation (F9) or calculate in VBA?

Michael

Niek Otten

unread,
Feb 10, 2009, 11:18:02 AM2/10/09
to
NOW() should give you what you require. Can you give an example of what you
get?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Michael.Tarnowski" <emt...@gmx.de> wrote in message
news:a3fd1fa7-923e-4245...@v19g2000yqn.googlegroups.com...

JE McGimpsey

unread,
Feb 10, 2009, 11:22:13 AM2/10/09
to
What is showing up in the formula bar when you copy-n-paste the cell
showing NOW()?

Are the cell(s) you're copying from in the same workbook as the cell(s)
you're copying to? Are the workbooks set to the same date format
(Tools/Options/Calculation)?

In article
<a3fd1fa7-923e-4245...@v19g2000yqn.googlegroups.com>,

Michael.Tarnowski

unread,
Feb 10, 2009, 11:37:39 AM2/10/09
to
To clarify my posting: I want to use a time stamping mechanism by
using a circular definition

=IF(D10=0;NOW();D10)

the point is: when should I use NOW() and when TODAY() to get a
timestamp like 10.Feb.2009 15:45 (actual dates and times)?

Michael

Michael.Tarnowski

unread,
Feb 10, 2009, 11:42:56 AM2/10/09
to
On Feb 10, 5:22 pm, JE McGimpsey <jemcgimp...@mvps.org> wrote:
> What is showing up in the formula bar when you copy-n-paste the cell
> showing NOW()?
>
> Are the cell(s) you're copying from in the same workbook as the cell(s)
> you're copying to? Are the workbooks set to the same date format
> (Tools/Options/Calculation)?
>
> In article
> <a3fd1fa7-923e-4245-b825-60142e304...@v19g2000yqn.googlegroups.com>,

>
> "Michael.Tarnowski" <emt...@gmx.de> wrote:
> > Hi community,
>
> > in excel help I found the information
>
> > TODAY():
> > "Returns the serial number of the current date. The serial number is
> > the date-time code used by Microsoft Excel for date and time
> > calculations. If the cell format was General before the function was
> > entered, the result is formatted as a date."
>
> > NOW():
> > "Returns the serial number of the current date and time. If the cell
> > format was General before the function was entered, the result is
> > formatted as a date.
> > ...
> > The NOW function changes only when the worksheet is calculated or when
> > a macro that contains the function is run. It is not updated
> > continuously. "
>
> > I have a cell formatted as TT. MMM JJ, hh:mm ;;
>
> > when I copy-n-paste a cell showing NOW() I get the current time but an
> > older date; when I copy-n-paste a cell showing today() I get the
> > current date but the time 00:00.
> > I want to have after copy-n-pasting the current date and the current
> > time. Do I have to force a re-calculation (F9) or calculate in VBA?
>
> > Michael

I want to realize a timestamping mechanism by using circular
references. In sheet A I have a cell with =IF(D10=0;NOW();D10); when
copy-n-pasting this row to sheet B an older (the last update of sheetA!
D10) is showed in sheet B; but I want the time of copy-n-pasting.

Michael

Michael.Tarnowski

unread,
Feb 10, 2009, 11:47:00 AM2/10/09
to

I realized that putting the cursor in the formula bar and hitting
<Return> before copy-n-pating updates the cell I want to copy, thus I
get the current time in sheet B when I paste. How can I force the
update of sheet A before copy-n-paste to sheet B?

Michael

David Biddulph

unread,
Feb 10, 2009, 12:29:08 PM2/10/09
to
You would need NOW().
TODAY() gives zero time on the current day.
--
David Biddulph

"Michael.Tarnowski" <emt...@gmx.de> wrote in message

news:0a00fb9b-d61b-4aff...@o11g2000yql.googlegroups.com...

Michael.Tarnowski

unread,
Feb 10, 2009, 2:36:27 PM2/10/09
to
On Feb 10, 6:29 pm, "David Biddulph" <groups [at] biddulph.org.uk>
wrote:

Thanks David, you affirmed my experiments
Michael

Message has been deleted

Michael.Tarnowski

unread,
Feb 10, 2009, 2:38:44 PM2/10/09
to
On Feb 10, 6:29 pm, "David Biddulph" <groups [at] biddulph.org.uk>
wrote:

Thanks David, you affirmed my experiments
Michael

Michael.Tarnowski

unread,
Feb 10, 2009, 4:45:12 PM2/10/09
to
Hi community,

I made the following experience: when implementing a time stamp
mechanismen a can chose between NOW() and TODAY().

But when I use NOW() I get the current date *and* time of a event, but
when using TODAY() I only get the actual date.
E.g. formatting a cell as TT. MMM JJ, hh:mm ;;

NOW() returns: 10. Feb. 09, 22:22
TODAY() returns 10. Feb. 09, 00:00,

thus TODAY() cuts the time information, whereas NOW() returns the
current date *and* current time, but has the drawback that the


function changes only when the worksheet is calculated or when a macro

that contains the function is run. It is not updated continuously,
(excerpt of Excel help).

For time stamping I use circular references like =IF(D10=0;NOW();D10).
This is coded in a cell/row in sheet A. In sheet B I want to insert
this row of sheet A by cut-n-paste, and the time stamp in sheet B
shall update to the time when the row (of sheet A) is inserted into
sheet B.

The problem I' am faced is, that =IF(D10=0;NOW();D10) shows when
inserted into B the *last* time update of sheet A, but not the current
time. Updating or re-calculating of sheet A has now effect since D10
is not equal zero - it shows the old value already.
The only way to update =IF(D10=0;NOW();D10) in sheet A to the current
date & time is to put the cursor into the formular bar and to hit
<return> *before* cut-n-pasting the row into sheet B.

Since I want to insert the timestamp into sheet B by pressing a button/
a macro in B, I'am looking for a mechanism to update sheet A to the
actual / current date & time and to insert it then into sheet B. I
would preferre a formula solution.

Any ideas?
Michael

P.S: sorry for multiple posting, I had already posted a similar
description but deleted the post undeliberatly.

Shane Devenshire

unread,
Feb 10, 2009, 5:03:01 PM2/10/09
to
Hi,

Both NOW and TODAY are volatile, which means they both recalculate whenever
the spreadsheet recalculates.

You can force recalculation by pressing F9.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

Michael.Tarnowski

unread,
Feb 11, 2009, 3:01:13 AM2/11/09
to
On Feb 10, 11:03 pm, Shane Devenshire

Hi Shane,
thank you for your reply. I understand that NOW() and TODAY() updates
when the sheet is recalculated. The problem is the field formula =IF
(D10=0;NOW();D10). This enters the old time stamp value when pasted
and never changes again since value is <> 0 - this is intended, the
time stamp should not chance after entered once. But the time stamp
should get the *current* time not the old one when entered.
Michael

0 new messages