EXCEL: create and keep date value

[dh42891]

I'm sure this has been addressed, but I need to calculate a cell with the date of the day another cell is filled in. Any suggestions? I know I can write something like: =IF(COUNTA(A1))=1,"date","") But I don't want Excel to recalculate the date everytime it refreshes. Thanks,

dylan

 

[AnotherHiggins]

You'll have to use VBA (a macro) to accomplish this.

See faq707-1657 for details.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[dh42891]

thanks, I didn't see that FAQ. That should do the trick.

dylan

 

[SkipVought]

dylan,

Could you be a bit clearer about what you are trying to accomplish?

If there is anything in A1, then COUNTA(A1)=1 will be TRUE.

Let's say there's a DATE in B1, then your formula might be...
[tt]
=IF(COUNTA(A1)=1,B1,"")
[/tt]
so the formula would either return the data in B1 or "".

Each time Excel calculates, it will execute the formula.


Skip,

_[red][/red]

 

[dh42891]

Sorry, the formula I threw out wasn't just to say I knew how to make it display the date, but not keep the correct time. The FAQ John directed me toward was perfect initially, but now I'm hoping to do that same thing for more than one pair of cells.

I have a data validation list of names. I would like Excel to store the date in B1 that someone selects a name in A1. I also would like this for D1 and C1, and F1 and E1, and so forth. Here is the working code for just one of those pairs, but I don't know how to extend the code. Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell As Range
    For Each Cell In Target
        With Cell
            If .Column = Range("B:B").Column Then
                Cells(.Row, "C").Value = Int(Now)
            End If
        End With
    Next Cell
End Sub

 

[dh42891]

nevermind, think I figured it out. thanks again!