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
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
"Michael.Tarnowski" <emt...@gmx.de> wrote in message
news:a3fd1fa7-923e-4245...@v19g2000yqn.googlegroups.com...
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>,
=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
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
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
"Michael.Tarnowski" <emt...@gmx.de> wrote in message
news:0a00fb9b-d61b-4aff...@o11g2000yql.googlegroups.com...
Thanks David, you affirmed my experiments
Michael
Thanks David, you affirmed my experiments
Michael
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.
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
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