How to find the date of the last activity

[MSSailor]

I have number of columns with date, distance, type of activity, type of
weather
Col A, Col B, Col C, Col D
"March 05 2009", "16", "swimming", "snowing"
"March 12 2009", "4", "walking", "snowing"
"March 11 2009", "21", "riding", "sunny"
"March 08 2009", "14", "cross country skiing", "snowy"
"March 03 2009", "4", "cross country skiing", "sunny"

Question:
Latest date when I was cross country skiing.
How to fix it, Had a very similar question for 30 min ago that helped me.

 

[NBVC]

Something like:

=Max(If($C$1:$C$100="cross country skiing",$A$1:$A$100))

Adjust ranges to suit and change text string to cell referenc
containing the text string...

Then confirm formula with CTRL+SHIFT+ENTER not just ENTER. You will se
{ } brackets appear around it..

Note: You may have to format the result cell as Dat

 

[T. Valko]

Assuming your dates are true Excel dates, try this array formula** :

=MAX(IF(C2:C6="cross country skiing",A2:A6))

Format as Date

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Chris Bode]

Add a command button from the control box on the sheet. Double click the
button to open the code window and paste following codes

Code:
--------------------

Private Sub CommandButton1_Click()
Dim row As Integer, col As Integer
row = 1
col = 1

Dim tmp As Date
tmp = CDate("March 08 1990")
While Sheet1.Cells(row, col).Value <> ""
If Sheet1.Cells(row, col + 2).Value = "cross country skiing" And CDate(Sheet1.Cells(row, col).Value) > tmp Then
tmp = CDate(Sheet1.Cells(row, col).Value)
End If
row = row + 1
Wend

MsgBox "Latest date=" & tmp
End Sub

--------------------


Now execute the codes and check the result


Chris