Twist on Timestamps.

[idunno]

I understand timestamps has been discussed before and I have gone through IE: (http://chandoo.org/wp/2009/01/08/timestamps-excel-formula-help/). However I have a twist on what I am trying to achieve.


I want to create a static timestamp as a formula without VBA that is caused by another event, but clears when that event is is again unfulfilled. In addition that event is caused not by physical input of the cell but an event in live fed data.


IE: A1 is a non-static value and A2 says =if(a1=1,1,0) and if A2 is 1 then shows what time it became 1 and empty "" if A2 = 0.


Have found some VBA work around but still troubling when I try to populate. I have been working on this issue over a year, hope someone will have some ideas.

 

[Luke M]

Would this work?

[pre]

Private Sub Worksheet_Calculate()
Dim StampCell As Range
'Which cell has timestamp?
Set StampCell = Range("A3")

Application.EnableEvents = False
If Range("A2") = 1 And StampCell = "" Then
StampCell = Now
ElseIf Range("A2") = 0 And StampCell <> "" Then
StampCell = ""
End If
Application.EnableEvents = True

End Sub

[/pre]

 

[idunno]

Ok, will try it out in the office tomorrow! Is there anyway I can set the event cells to be like 1 every 6 cells, IE a3,a9,a15

 

[Luke M]

How about this?

[pre]

Private Sub Worksheet_Calculate()
Dim OverallRange As Range
Dim c As Range
Dim StampCell As Range

'What is the complete range of cells to look at?
Set OverallRange = Range("A3:A100")

Application.EnableEvents = False
For Each c In OverallRange
'Check if cell is of interest
'IE, check every 6th row starting at row 3
If c.Row - 3 Mod 6 = 0 Then
'This controls which cell gets timestamp relative to cell c
'Currently puts stamp 1 row below cell c
Set StampCell = c.Offset(1, 0)
If c.Value = 1 And StampCell = "" Then
StampCell = Now
ElseIf c.Value = 0 And StampCell <> "" Then
StampCell.ClearContents
End If
End If
Next
Application.EnableEvents = True

End Sub

[/pre]

 

[idunno]

Luke thanks very much for your help, could you advise me if I have the events of 1 or 0 in AE 8 and AE 9 , and need timestamps in AC12 and AC13 and then every 16 cells down IE: AE 24 and AE 25 being the next and AC28 and AC29 for timestamps related to those becoming 1.


Also is there a way to force the output to come in MM/DD/YYYY hh:mm format, IE 8/30/2011 16:15

 

[Luke M]

For the format, simply format the cells in XL to:

mm/dd/yyyy [h]:mm


For your criteria, change this line of code:

[pre]

If c.Row - 3 Mod 6 = 0 Then
to this:

If c.Row - 8 Mod 16 = 0 Or c.Row - 9 Mod 16 = 0 Then

[/pre]
However, if the cells are next to each other, I would think that would mess up how the time stamps are placed. You might want to change the c.Offset(1,0) part if the time stamp is not supposed to be in the cell directly below.