Insert date when given value is true, then don't update.

Posted by S.W. on January 28, 2002 10:37 PM

I am creating a task tracking worksheet, and would like to automatically insert the date on which a particular task was completed. I have done with the following formula: =IF(E11=100%,TODAY(),"")

However, once the task is 100% complete, I do not want the date to be updated automatically. Therefore it appears TODAY() might not be the best function to use, or perhaps I need to add other functions to the formula.

Posted by Dan Aragon on January 29, 2002 5:51 AM

Is E11 a value that you key in, or is it a formula? If it is a value that you key in, you can use a VBA procedure that does the same thing as the formula you wrote below.

Assuming that you are always entering the percentages in column E and you want the Date in column F, you would paste this code in to the Worksheet you want this to run on in the VBA editor (Alt-F11).

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 5 Then
If Target.Value = 1 Then
Target.Offset(0, 1) = Date
Else:
Target.Offset(0, 1) = ""
End If
End If
End Sub

Reply back if you have questions. HTH.

Posted by S.W. on January 29, 2002 8:50 PM

Thanks for your reply Dan.

In answer to your question, yes, the value entered into column E will always be a percentage. I would then like the date that value was entered to be generated automatically in the same row of column I.

I have put the code into the worksheet as you suggested. Am I in assuming this is a Macro? If so, do I need to run it every time I enter a percentage in column E? I am a novice when it comes to Macros, so I'm not sure what I need to do now. Can you help?
TIA

Posted by Dan Aragon on January 30, 2002 6:41 AM

First, if you want the date to appear in column I, change the part of the code that says Target.Offset(0,1) to Target.Offset(0,4). This part of the code is where the date value gets put (0 rows down, and 4 columns to the right of the cell in column E).

Second, this is a macro that should run whenever there is a change in a cell in column E. You should not have to do anything. However, it must be put in the correct spot in your workbook, otherwise it won't run. Here's how:

First, save a backup copy of your workbook! Open the VB editor by hitting Alt-F11. You should see a window titled "Project-VBA Project". Inside that window, you should see your workbook name, and under that each worksheet name. Find the worksheet that you want this to work in, and double-click on it. That should bring up a blank window with the worksheet name in the title bar. Paste the code in there. Hit Alt-Q to exit and see if the code works by entering a 100% in column E.

Note that this code does not remove the date if you change the percentage from 100% to something else. The date remains and you will need to delete the date yourself. If that's an issue, let me know. Reply back with any other questions.

Dan

Posted by S.W. on January 31, 2002 10:01 PM

ERR