ExcelBanter - How to find the date of the last activity

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - How to find the date of the last activity (https://www.excelbanter.com/excel-discussion-misc-queries/224294-how-find-date-last-activity.html)

MSSailor

How to find the date of the last activity

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[_4_]

How to find the date of the last activity

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 reference
containing the text string...

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

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

--
NBVC

Where there is a will there are many ways.
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=75178

T. Valko

How to find the date of the last activity

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.

--
Biff
Microsoft Excel MVP

"MSSailor" wrote in message
...
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.

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
------
Convert your Excel spreadsheet into an online calculator.
http://www.spreadsheetconverter.com

All times are GMT +1. The time now is 11:27 PM.