Recording the first change in the value of a cell

[carl]

I have a spread sheet that looks like so before the start of the trading day:

ColA ColB ColC
ABC 4/7/2005

ColB tracks the trade time of stock ABC.

When ABC does it's first trade, the spreadsheet looks like so:

ColA ColB ColC
ABC 9:32:28AM

I would like to place a formula in ColC or find a way to record the time of
the first trade.

Is it possible ?

Thank you in advance.

 

[Biff]

Hi!

I'm sure this is a piece of cake with some VBA code but I
can't help you with that.

Here's a way to do this but it's crude:

Before any trades take place and your sheet still looks
like:

ColA ColB ColC
ABC 4/7/2005

Use an intentional circular reference:

Goto Tools>Options>Calculation

Check Iteration and set Maximum Iterations to 1.

Now, enter this formula in C1 with the key combo of
CTRL,SHIFT,ENTER:

=MIN(IF(B1:C1>0,B1:C1))

Format the cell as TIME.

With the date in cell B1, C1 will return 12:00 AM. As soon
as the FIRST trade takes place and cell B1 is updated with
a time, cell C1 will also record that time.

Drawback:

You would have to repeat this process every day. At the
end of the day you could just convert the formula to a
TEXT string by preceding it with an apostrophie and then
in the morning delete the apostrophie hit CTRL,SHIFT,ENTER
and your set for that day.

Biff