Static date stamp vs. volatile

one way:

A combination of phantom's suggestions:

1) Hit alt + F11 to get to the vb editor
2) go insert | module
3) paste in the following:

Function DateAndTime()
DateAndTime = Now
End Function

4) use it in a formula of the sort:

=if(a1=100%,dateandtime(),"")

which should produce the desired stamp.

paddy

Sorry, Paddy, but that won't work!

Just force recalculate (CTRL-ALT-SHIFT-F9) and the value will change.

The only two ways that come to mind right now (and work) are:

a) Use a Worksheet_change event procedure to monitor %complete and update the date cell when appropriate

b) Use an iterative solution (not the most pleasant of thoughts, eh?). Turn on iteration (Tools | Options... | Calculation tab) and set #iterations to 1.

Now, suppose A10 has the % complete (0 up to 1 formatted as %). Then, in B10, enter the formula <pre>=IF(A10<>1,"",IF(OR(B10="",B10=0),NOW(),B10))</pre>

"Sorry, Paddy, but that won't work! "

That'll teach me to bastardise a previous solution without thinking about the current problem enough.

Paddy

On relection, it probably wont

try expanding the Function to the following

Function DateAndTime()
DateAndTime = Now
DateAndTime = Format(DateAndTime, "dd-mmm-yyyy h:mm")
With Selection
.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End With
Application.CutCopyMode = False

End Function


try with formula like

=IF(C2=1,DateAndTime(),"") for individual project

criteria based on group

=IF(COUNTIF($C$2:$C$8,"<1"),"",DateAndTime())

The C2 etc can be formatted as %

Also I used Date and Time to test; edit the format part to your preferences/requirements.



DO NOT USE THIS SUGGESTION.
THE INFORMATION IS STATIC; HOWEVER, A VARIETY
OF ACTIONS MAKE THE INFO DYNAMIC AND
INFORMATION BECOME CURRENT.

Use Shortcut keys or VBA.

davidalmbe
did not have the courtesy to advise if he tested the suggestion.
This message was edited by Dave Patton on 2002-12-04 00:32

Sorry, Dave, that too doesn't work. Just try it. CTRL-ALT-SHIFT-F9 and it will recalculate.

Worse, the function violates XL's rules for what a function can do, which is return a value and *not* attempt to modify the environment.

In this case, XL executes the Copy and PasteSpecial statements but doesn't do anything with them. On my machine, XL locked up until I did a bunch of random ESCs, ALT-TABs, and mouse clicks.

On 2002-12-02 23:21, tusharm wrote:
Sorry, Dave, that too doesn't work. Just try it. CTRL-ALT-SHIFT-F9 and it will recalculate.

Worse, the function violates XL's rules for what a function can do, which is return a value and *not* attempt to modify the environment.

In this case, XL executes the Copy and PasteSpecial statements but doesn't do anything with them. On my machine, XL locked up until I did a bunch of random ESCs, ALT-TABs, and mouse clicks.

Click to expand...

The suggestion works OK with Excel 97.
I did not test it with any other versions.

Total recalculation sets the cell(s)
to the current Date/Time; this may or not
be a feature.

One can format the cells with regular formatting or Custom Format instead of doing it in the function.

To lock the information, copy the range with Dates and PasteSpecial as value.
One can do this with a small macro.



DO NOT USE THIS SUGGESTION.
THE INFORMATION IS STATIC; HOWEVER, A VARIETY
OF ACTIONS MAKE THE INFO DYNAMIC AND
INFORMATION BECOME CURRENT.

Use Shortcut keys or VBA.

davidalmbe
did not have the courtesy to advise if he tested the suggestion.
This message was edited by Dave Patton on 2002-12-04 00:33

This did the trick for me:

<code>Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If [A1] = "1" Then
'If A1 is formatted as text, then you will need to change "1" to "100%"
If [E1] = "" Then
[E1] = Now
End If
End If
End Sub</code>

Thanks everyone. I have it all set up now and it is working fine. You all came through in a pinch. I tested it last night and double checked the data this morning. No issues. I used Paddy's first reply and I haven't had any issues thus far. If I do, I will respond and let you know.

Thanks again

This has got to be of use to someone and somehow.

http://216.92.17.166/board/viewtopic.php?topic=8657&forum=2