Help with date automatic date problem (probably very simple)...

  • Thread starter Thread starter Dave E
  • Start date Start date
D

Dave E

Hello

Could anyone offer any help with this problem, it is probably very ver
simple, but I am a beginner to Excel.

I would like a date to be entered into Cell D1, when a number '1' i
entered into *either* A1, B1, *OR* C1.

I am using the following:

=IF(OR(A1=1, B1=1, C1=1),TODAY(),"")

Now this works, however it enters the current system date at all times
and changes day to day. What I need it to do is to enter and store th
date that the '1' is entered into the first 3 columns. So if the 1 i
entered on 01/01/03, that date will stay forever.

Does that make sense to anyone? Can anyone help a newbie? :)

Cheers

Dave:cool
 
Dave,

If you have any experience with macros, cut and paste this one into the
macro sheet for the sheet you are working on.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo ExitSub
If Target.Column < 4 And Target.Value = 1 And
IsEmpty(Cells(Target.Row, 4)) Then Cells(Target.Row, 4) = Date
ExitSub:
End Sub

I found I needed the error handler to pick up when you do a "mass"
range deletion. Other than that, it should work as long as you enter
the 1's one cell at a time.

Hope this helps and let me know if you need help with getting to your
macro sheet.

JerryG :)
 
It does work as you have it written, just right click and format
for however you want the date displayed.


Lane
 
Thanks guys, but my date function works, the problem is that it displays
the current date at all times, not the date of when the '1' was
entered... so although today it will display 2/12, tomorrow it will be
displaying 3/12. :( It needs to stay as the date when the '1' was
entered into columns A, B, or C.

:)
 
Dave,

A formula using NOW() or TODAY() will always update itself whenever you
open the file. The problem is I don't think you will find any formula
that would work the way you are trying to make it work. The macro will
only insert the current date the first time a 1 is entered in one of
your first three columns. It will never change that date, whether you
enter another 1 in one of the other columns or open the file at a later
date.

JerryG :)
 
Dave

Worksheet Event OK??

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column > 3 Then GoTo enditall
Set myrng = Range("A1:C1")
n = Target.Row
If Target.Row > myrng.Rows.Count Then GoTo enditall
For Each cell In myrng
If cell.Value = 1 Then
Range("D" & n).Value = Now
End If
Next
enditall:
Application.EnableEvents = True
End Sub

Right-click on worksheet tab and "View Code". Paste in there.

As written will operate only on row 1.

You can change the range from, say A1:C10

Gord Dibben XL2002
 
Back
Top