Creating a Timestamp

[edthehorse]

Hey

i'm very much a noobie with VBA, i've managed to play around with different macros and functions but i've been banging my head against a wall trying to figure out this one

I've tried some simple functions and found one or two that worked but nothing did exactly what i wanted, the latest one worked fine until the cell that triggered the boolean that started the timestamp updated and the timestamp would also update.

i was trying to maybe use a Static variable but then i'm not sure how to Reset the value.

[vba]Function Timestamper (Cell, Condition)

If Evaluate(Cell.Value & Condition) Then
Timestamper = Now

Else
Timestamper = TimeValue("00:00:00")

End If
Exit Function

End Function[/vba]
the cell referenced is a bool that flips from 0 to 1 based on a formula. A cell in the formula also references a cell that records the max of another cell, even though the max doesnt change when the cell its recording the max does the timestamp reprints now.

Ideally i could make the initial 'NOW' stamp a static variable that would only change when something resets it..

I tried playing with some code for recording and resetting a running max function that i found on the forum but i cant get it to do what i want!

Any help would be appreciated

Thanks in Advance

[Bob Phillips]

So what are you trying to do, get a timestamp when the cell with the Boolean changes?

How about a workbook example?

[edthehorse]

yeah basically to get a timestamp when a bool changes that will stay that way until the bool flicks back to 0, in which case it will print 00:00:00.

This works if i manually change the bool but in this case the bool is reliant on another cell, even though it doesnt change it still reprints whenever the other cell recalculates.

i'll try and get an example uploaded

[edthehorse]

heres an example of what i am trying to do, note that if you change the cell in column a eventhough the Bool stays at 1 the timestamp reprints.

i would like it to either stay at the same timestamp until i change a reset bool like the runningmax fn or that it would only update when the bool from the cell C4 turns to zero

any ideas?

cheers

[edthehorse]

a more practical example, in the last one the bool would always stay at one whereas this one can change to 0 when reset

[edthehorse]

Hey XLD,

any ideas?!

[snb]

[VBA]Function Timestamper(c01)
Timestamper = c01 * Now
End Function[/VBA]

=timestamper(D4)

[edthehorse]

VBA:

Function Timestamper(c01) Timestamper = c01 * Now End Function

VBA tags courtesy of www.thecodenet.com

=timestamper(D4)

thanks for trying snb but that solution has the same issue as the first, it will keep updating. Also if the sheet is calculating (it will autocalculate every second) that would give me a running clock as oppose to a timestamp..

I really need some kind of static value that can be reset i think.. like the one used in the running max fn but i've tried playing around with it and just can't get it to do the same thing with the time.

thanks for trying all the same snb!

[snb]

I wasn't trying.
You have to rethink your goal.
It's no problem to fix a value, but in that case you shouldn't use a function (UDF); nor should you introduce 2 values: 0 or the timestamp; nor should make the timestamp dependent of a variable value.
[vba]
sub snb()
if cells(4,5)="" and cells(3,5)=1 then cells(4,5)=now
end sub
[/vba]

[edthehorse]

I wasn't trying.
You have to rethink your goal.
It's no problem to fix a value, but in that case you shouldn't use a function (UDF); nor should you intoduce 2 values: 0 or the timestamp; nor should make the timestamp dependent of a variable value.
[VBA]
sub snb()
if cells(4,5)="" and cells(3,5)=1 then cells(4,5)=now
end sub
[/VBA]

I was trying to be polite and thanking you for your post,

that would not help me at all as it basically changes what i was trying to do into something completely different.

the reason i want to use a function is that i need to run this same function across a large array of data and create timestamps on a number of different variable bools, if i use a function that references a cell and relies on a condition it is far more flexible than coding in specific cells.

I have found plenty of ways to print in a timer or static stamp based on a condition that would serve me fine if thats what i was looking to do, but the issue i am looking to resolve is how to fix that value and reset it as i please.
Unfortunately i can't just completely change what i am trying to do to make the problem easier to solve.