| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
|
|||
|
|||
|
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
Chris ------ Convert your Excel spreadsheet into an online calculator. http://www.spreadsheetconverter.com |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Networkdays include weekend day if activity | Excel Discussion (Misc queries) | |||
| daily activity time log | Excel Discussion (Misc queries) | |||
| activity duration | Setting up and Configuration of Excel | |||
| Running Activity Chart | Charts and Charting in Excel | |||
| Display material and activity | Excel Worksheet Functions | |||
Linear Mode
