N
newman
Is there a function available [not a macro] to timestamp a cell when an
entry is made into another cell?
Regards
entry is made into another cell?
Regards
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
S
squenson
No, one of the basics of spreadsheet software is that you cannot update the
content of another cell with a fixed value when you modify a cell. Only a
macro can do it.
content of another cell with a fixed value when you modify a cell. Only a
macro can do it.
E
Earl Kiosterud
Newman,
This formula, in D2, will latch the current date-time in D2 when an entry is made in A2. If
A2 is changed, it won't change the time -- it will still show when the first entry was made.
You have to have set Iterations on (Tools - Options - Calculation). The iterations count
can be 1. To reset the formula, select D2, press F2, then Enter.
=IF(AND(A2<>"",D2=0),NOW(),D2)
Format the cell (Format - Cells - Number - Custom, with something like:
h:mm AM/PM;;
h:mm:ss AM/PM;; (for seconds)
h:mm:ss.00 AM/PM;; (for seconds and fractional seconds)
m/d/yy h:mm:ss.00 AM/PM;; (includes the date)
If you want the indicated time to change any time the target cell is changed (indicating the
last time it was changed, not the first time), that will take some tweaking. Or maybe
better, as another responder said, a macro.
--
Earl Kiosterud
www.smokeylake.com
Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
This formula, in D2, will latch the current date-time in D2 when an entry is made in A2. If
A2 is changed, it won't change the time -- it will still show when the first entry was made.
You have to have set Iterations on (Tools - Options - Calculation). The iterations count
can be 1. To reset the formula, select D2, press F2, then Enter.
=IF(AND(A2<>"",D2=0),NOW(),D2)
Format the cell (Format - Cells - Number - Custom, with something like:
h:mm AM/PM;;
h:mm:ss AM/PM;; (for seconds)
h:mm:ss.00 AM/PM;; (for seconds and fractional seconds)
m/d/yy h:mm:ss.00 AM/PM;; (includes the date)
If you want the indicated time to change any time the target cell is changed (indicating the
last time it was changed, not the first time), that will take some tweaking. Or maybe
better, as another responder said, a macro.
--
Earl Kiosterud
www.smokeylake.com
Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
G
Guest
Maybe this..........
=A1+NOW()-A1
Vaya con Dios,
Chuck, CABGx3
=A1+NOW()-A1
Vaya con Dios,
Chuck, CABGx3
S
Sandy Mann
Hi Chuck,
NOW() will of course recalculate whenever there is *any* calculation in the
worksheet.
--
Regards,
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
NOW() will of course recalculate whenever there is *any* calculation in the
worksheet.
--
Regards,
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
CLR said:Maybe this..........
=A1+NOW()-A1
Vaya con Dios,
Chuck, CABGx3
newman said:Is there a function available [not a macro] to timestamp a cell when an
entry is made into another cell?
Regards
G
Guest
Well, at least I'm keeping you on your toes today
Sandy..........<G>.....thanks for the catch.
"Time for the rest of my medication now......."
Vaya con Dios,
Chuck, CABGx3
Sandy..........<G>.....thanks for the catch.
"Time for the rest of my medication now......."
Vaya con Dios,
Chuck, CABGx3
Sandy Mann said:Hi Chuck,
NOW() will of course recalculate whenever there is *any* calculation in the
worksheet.
--
Regards,
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
CLR said:Maybe this..........
=A1+NOW()-A1
Vaya con Dios,
Chuck, CABGx3
newman said:Is there a function available [not a macro] to timestamp a cell when an
entry is made into another cell?
Regards
E
Earl Kiosterud
Hey Chuck,
Don't feel bad. I spent quite a while trying to get my formula to latch the time of the
latest change (instead of only the first change). It fought me all the way. I'd forgotten
that NOW() recalculates at any calculation. And that's exactly what kept happening. Deet
dee dee!
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
Don't feel bad. I spent quite a while trying to get my formula to latch the time of the
latest change (instead of only the first change). It fought me all the way. I'd forgotten
that NOW() recalculates at any calculation. And that's exactly what kept happening. Deet
dee dee!
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
CLR said:Well, at least I'm keeping you on your toes today
Sandy..........<G>.....thanks for the catch.
"Time for the rest of my medication now......."
Vaya con Dios,
Chuck, CABGx3
Sandy Mann said:Hi Chuck,
NOW() will of course recalculate whenever there is *any* calculation in the
worksheet.
--
Regards,
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
CLR said:Maybe this..........
=A1+NOW()-A1
Vaya con Dios,
Chuck, CABGx3
:
Is there a function available [not a macro] to timestamp a cell when an
entry is made into another cell?
Regards
G
Guest
Yeah, thanks........but that's what I get for not testing my recommendation
beyond just the immediate requirement.........Harlan taught me that one time,
but I guess it just hasn't sunk in yet........sometimes the Dragon wins <G>
Vaya con Dios,
Chuck, CABGx3
beyond just the immediate requirement.........Harlan taught me that one time,
but I guess it just hasn't sunk in yet........sometimes the Dragon wins <G>
Vaya con Dios,
Chuck, CABGx3
Earl Kiosterud said:Hey Chuck,
Don't feel bad. I spent quite a while trying to get my formula to latch the time of the
latest change (instead of only the first change). It fought me all the way. I'd forgotten
that NOW() recalculates at any calculation. And that's exactly what kept happening. Deet
dee dee!
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
CLR said:Well, at least I'm keeping you on your toes today
Sandy..........<G>.....thanks for the catch.
"Time for the rest of my medication now......."
Vaya con Dios,
Chuck, CABGx3
Sandy Mann said:Hi Chuck,
NOW() will of course recalculate whenever there is *any* calculation in the
worksheet.
--
Regards,
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
Maybe this..........
=A1+NOW()-A1
Vaya con Dios,
Chuck, CABGx3
:
Is there a function available [not a macro] to timestamp a cell when an
entry is made into another cell?
Regards
N
newman
Thanks
That is a good solution. Is it possible to have the cell D2 blank if
cell A2 is empty. I will be copying the formula down a number of rows.
Regards
Thanks
That is a good solution. Is it possible to have the D2 cell blank if
cell A2 there is
That is a good solution. Is it possible to have the cell D2 blank if
cell A2 is empty. I will be copying the formula down a number of rows.
Regards
Earl said:Newman,
This formula, in D2, will latch the current date-time in D2 when an entry is made in A2. If
A2 is changed, it won't change the time -- it will still show when the first entry was made.
You have to have set Iterations on (Tools - Options - Calculation). The iterations count
can be 1. To reset the formula, select D2, press F2, then Enter.
=IF(AND(A2<>"",D2=0),NOW(),D2)
Format the cell (Format - Cells - Number - Custom, with something like:
h:mm AM/PM;;
h:mm:ss AM/PM;; (for seconds)
h:mm:ss.00 AM/PM;; (for seconds and fractional seconds)
m/d/yy h:mm:ss.00 AM/PM;; (includes the date)
If you want the indicated time to change any time the target cell is changed (indicating the
last time it was changed, not the first time), that will take some tweaking. Or maybe
better, as another responder said, a macro.
--
Earl Kiosterud
www.smokeylake.com
Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------
newman said:Is there a function available [not a macro] to timestamp a cell when an
entry is made into another cell?
Regards
Thanks
That is a good solution. Is it possible to have the D2 cell blank if
cell A2 there is
G
GSalisbury
newman said:Thanks
That is a good solution. Is it possible to have the cell D2 blank if
cell A2 is empty. I will be copying the formula down a number of rows.
Regards
Earl said:Newman,
This formula, in D2, will latch the current date-time in D2 when an entry is made in A2. If
A2 is changed, it won't change the time -- it will still show when the first entry was made.
You have to have set Iterations on (Tools - Options - Calculation). The iterations count
can be 1. To reset the formula, select D2, press F2, then Enter.
=IF(AND(A2<>"",D2=0),NOW(),D2)
Format the cell (Format - Cells - Number - Custom, with something like:
h:mm AM/PM;;
h:mm:ss AM/PM;; (for seconds)
h:mm:ss.00 AM/PM;; (for seconds and fractional seconds)
m/d/yy h:mm:ss.00 AM/PM;; (includes the date)
If you want the indicated time to change any time the target cell is changed (indicating the
last time it was changed, not the first time), that will take some tweaking. Or maybe
better, as another responder said, a macro.
--
Earl Kiosterud
www.smokeylake.com
Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------
Is there a function available [not a macro] to timestamp a cell when an
entry is made into another cell?
Regards
Thanks
That is a good solution. Is it possible to have the D2 cell blank if
cell A2 there is
Here is a method I use.
I think you should be able to adapt it.
The key to both is Tools/Options/Calculation/Iteration "on".
There's two expressions.
One for Date:
=IF( AND(B17="",F17="",J17="",N17=""), "", IF(A17="",NOW(), A17 ) )
One for Time:
=IF( AND(E17="",C17="",D17=""), "", IF(B17="",ROUND(NOW()*$A$1,0)/$A$1,
B17 ) )
The $a$1 is a value of 288 that rounds the time to the nearest five
minutes as that's all I wanted.
I got the Iteration control and rounding technique here in this NG - it's a
great source!
What it does is, I have lines that have four "blocks" of three "sets" each
and the first time [any] block is touched the date is recorded and then the
time is recorded as each set is touched. If something is not touched the
dates and times are left are they are. This sounds like what you want.
Geo. Salisbury
Long Valley, NJ