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.
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...
C
CLR
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
[email protected]
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
[email protected]
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
C
CLR
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
[email protected]
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
[email protected]
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
C
CLR
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
[email protected]
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