Is it possible to timestamp a cell?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

newman - 26 Jul 2007 10:16 GMT

Is there a function available [not a macro] to timestamp a cell when an
entry is made into another cell?

Regards

Reply to this Message

squenson - 26 Jul 2007 11:36 GMT

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.

>Is there a function available [not a macro] to timestamp a cell when an
>entry is made into another cell?
>
>Regards

Reply to this Message

Earl Kiosterud - 26 Jul 2007 15:02 GMT

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.

Signature

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

Reply to this Message

newman - 27 Jul 2007 12:20 GMT

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

> Newman,
>
[quoted text clipped - 28 lines]
> >
> > Regards

Thanks

That is a good solution. Is it possible to have the D2 cell blank if
cell A2 there is

Reply to this Message

GSalisbury - 27 Jul 2007 12:47 GMT

> Thanks
>
[quoted text clipped - 40 lines]
> 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

Reply to this Message

CLR - 26 Jul 2007 16:42 GMT

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

Reply to this Message

Sandy Mann - 26 Jul 2007 17:59 GMT

Hi Chuck,

NOW() will of course recalculate whenever there is *any* calculation in the
worksheet.

Signature

Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Maybe this..........
>
[quoted text clipped - 7 lines]
>>
>> Regards

Reply to this Message

CLR - 26 Jul 2007 18:18 GMT

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

> Hi Chuck,
>
[quoted text clipped - 12 lines]
> >>
> >> Regards

Reply to this Message

Earl Kiosterud - 27 Jul 2007 05:00 GMT

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!

Signature

Earl Kiosterud
www.smokeylake.com

-----------------------------------------------------------------------

> Well, at least I'm keeping you on your toes today
> Sandy..........<G>.....thanks for the catch.
[quoted text clipped - 20 lines]
>> >>
>> >> Regards

Reply to this Message

CLR - 27 Jul 2007 12:08 GMT

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

> Hey Chuck,
>
[quoted text clipped - 26 lines]
> >> >>
> >> >> Regards

Reply to this Message