NOW() and storing cells

[Calvin]

I am trying to store the date and time in a cell using the NOW(
function. Im using an IF statement like such: =IF(B16>0,NOW(),""), s
when the value of colum B is greater than 0 it will populate the dat
and time for that row. The probem is each time I populate the B cel
the date and time is changed to the current time for the entire dat
time column. How do I keep just the value of when that row was entered

 

[JE McGimpsey]

Take a look at

http://www.mcgimpsey.com/excel/timestamp.html

 

[A.W.J. Ales]

Calvin,

That is just what NOW() is supposed to do.
If you want to enter a fixed date use : Cntr + ;
If you want to enter a fixed time use : Cntr + Shif + ;

I don't know how to enter them together, but you cn add them after they were
entered to get a day+time (if you also format the cell that way).
Or you could use this macro to enter that in your active cell.
(It can be written as a function as well, but that will sometimes change the
value of the cell again).

Sub AADateTime()
A = Now()
ActiveCell.Value = A
End Sub

To enter this macro : Press F11 ( invoking the VB editor), look for the
project with your filename and select it.
Then Insert module and paste the macro into that module.
Press the X at the right of the screen to get back to the "normal" Excel
environment and your macro should be working
(It is saved with your workbook).

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[A.W.J. Ales]

Apart from that all, you could offcourse also just enter the date and time
with Now() and then Copy / PasteSpecial Values.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

Calvin,

That is just what NOW() is supposed to do.
If you want to enter a fixed date use : Cntr + ;
If you want to enter a fixed time use : Cntr + Shif + ;

I don't know how to enter them together, but you cn add them after they were
entered to get a day+time (if you also format the cell that way).
Or you could use this macro to enter that in your active cell.
(It can be written as a function as well, but that will sometimes change the
value of the cell again).

Sub AADateTime()
A = Now()
ActiveCell.Value = A
End Sub

To enter this macro : Press F11 ( invoking the VB editor), look for the
project with your filename and select it.
Then Insert module and paste the macro into that module.
Press the X at the right of the screen to get back to the "normal" Excel
environment and your macro should be working
(It is saved with your workbook).

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Calvin]

I wasnt really expecting an answer so fast but McGlimpsey hit the nail
on the nail and now is work for all other newbies "like myself"...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))

Thanks again, all.