Get timestamp when formula changes value of a cell

[ErikMass]

Hi Excel people,

I've been looking around for a solution to my problem, but only a partial solution exists so I kindly ask you for your help.

I have an excel sheet in which I have the following:

B1 = "clock" => now()-today()

B3 = "time_threshold" => set to "13:00:00" in this example now

A6 = "formula" => "=IF(B1>B3,1,0)"

Now, when B1 > B3 then I get a "1" in A6. When this happens i want in B6 the timestamp when this happens.

(the partial solutions i found only make it work when the cell A6 is actually activated (manually), but I want the timestamp purely when the formula changes the value in A6 from 0 to 1, without me touching the excel with my mouse).

 

[mrhstn]

Have you tried using the Calculate event for this.

In the worksheet module of the sheet housing your clock, threshold and formula try something like

Private Sub Worksheet_Calculate()
If Range("A6") = 1 Then Range("B6").Value = DateTime.Now
End Sub

 

[Stiuart_W]

Have you tried using the Calculate event for this.

In the worksheet module of the sheet housing your clock, threshold and formula try something like

Private Sub Worksheet_Calculate()
If Range("A6") = 1 Then Range("B6").Value = DateTime.Now
End Sub

I assume the OP wants B6 to be updated with the timestamp when A6 is FIRST 1. What you have posted will just keep updating the value of B6 with the current time. What he could do is create a helper column/cell

Something like this:-

Private Sub Worksheet_Calculate()
  If Range("A6") = 1 and range("AA6") ="" Then 
    Range("B6").Value = DateTime.Now
    Range("AA6")="Changed"
  End if 
End Sub

 

[ErikMass]

Hi,

Thanks for your quick responses. It might work, but I run into a problem, since the problem I specified in reality is a bit more elaborate.

I told you that I have in Cell A6 a formula "=If(B1>B3,1,0), but in real life I have a formula like the latter one (yet not the same) in Cells A6 to A1000 and each "An" can be triggered by this formula to produce a 0 or 1 as a result. Now what I require is that when any of those formulas in Range("A6:A1000") jumps to 1, behind the corrosponding "An" I want the timestamp.

Sorry I was not clear at first, but I thought that when I got an answer for the easy problem, I might be able to solve the more difficult one myself (which i can't apparently).

And Stiuart_W, you are right, I want to know when the Cell becomes 1 for the first time.

Erik

 

[ErikMass]

Hi,

It seems that my problem is a bit too difficult to tackle. In essence the Problem can be summarized as follows:

In a column Range("A6:A1000") if have formula's in each cell. The outcome of that formula is usually 0 or empty, but a few times a day, a few formula's in that range will generate a 1 when a condition is met (but, in all cases, the 0, empty or 1 are generated by a formula).

What I need to know is for those few cases a day, the time that the cell value flips from 0 to 1 (or from empty to 1). So I need the first instance that the cell value flips. So for example, cell A158 changes (by formula) to 1, I need the time that this happened an be places in B158 and if it flips in A854, I need the time of the flip in B854.

I really hope that anyone can help me with this, since I just cannot make it work. Any help is much appreciated.

Erik

 

[ErikMass]

To add one more thing, I solved the problem already using VBA code that runs continuously, but my excel gets so heavy that I need a different solution. I need something that is only invoked when the cell value flips and not something like a continuous running VBA script.

Thanks,
Erik