Formula to return time and date based on distance condition across multiple worksheets

[F0r3stH1k3r]

I have a multi-sheet workbook for tracking my runs. Each month has its own sheet with identical columns which track Miles and Time, among other things.

I'd like to track my Personal Record (PR) time [cell H5] and the date that time was achieved [cell H6]. I've done this with a MIN formula in H5 and an Index/Match in H6. What I can't figure out is how to return Time and Date only on condition that Miles are >=3.11.

I'd also like this formula to take all worksheets into consideration. If I get a PR in January, I'd like that time and date to show in later months until it has been surpassed.

 

[HighAndWilder]

I have a multi-sheet workbook for tracking my runs. Each month has its own sheet with identical columns which track Miles and Time, among other things.

I'd like to track my Personal Record (PR) time [cell H5] and the date that time was achieved [cell H6]. I've done this with a MIN formula in H5 and an Index/Match in H6. What I can't figure out is how to return Time and Date only on condition that Miles are >=3.11.

I'd also like this formula to take all worksheets into consideration. If I get a PR in January, I'd like that time and date to show in later months until it has been surpassed.

Welcome to Mr Excel.

As is often the case, it would be easier if the raw run data is contained in one sheet in a table and the summary sheet accesses this information as appropriate.
The adoption of this approach will make any analysis much easier.

I have mocked something up to demonstrate.

A means has to be developed to change the month (actually stored as the first day in the month) on the Analysis sheet.

Formula can be developed to give you the results that you want on the Analysis sheet.

Let me know if you want to proceed in this way.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Day	Miles	Time	Pace (mph)	Location *	Shoes	Results	Place	Gender	Age
2	25/01/2025	Saturday	6	00:48:00	7.46
3	26/01/2025	Sunday	6	00:51:00	6.95
4	27/01/2025	Monday	6	00:46:00	7.69
5	28/01/2025	Tuesday	6	00:37:00	9.63
6	29/01/2025	Wednesday	19	02:01:00	9.42
7	30/01/2025	Thursday	14	01:26:00	9.66
8	31/01/2025	Friday	13	01:40:00	7.74
9	26/02/2025	Wednesday	16	02:00:00	7.99
10	27/02/2025	Thursday	5	00:30:00	9.97
11	28/02/2025	Friday	17	02:00:00	8.46
12	01/03/2025	Saturday	12	01:29:00	8.01
13	02/03/2025	Sunday	17	02:27:00	6.91
Run Data

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		January 2025
3
4		PROGRESS				RESULTS		PR	SHOES
5		Goal	19:18	Days Left	-	# 5k+ days			Ghost GTX
6		Month to Date	---	Req'd/day	-	# days			Launch GTS
7		Left to go	19:18	Avg/day	-				Vionic
8
9		Date	Day	Miles	Time	Pace (mph)	Location *	Shoes	Results	Place	Gender	Age
10		25/01/2025	Saturday	6	00:48:00	7.46
11		26/01/2025	Sunday	6	00:51:00	6.95
12		27/01/2025	Monday	6	00:46:00	7.69
13		28/01/2025	Tuesday	6	00:37:00	9.63
14		29/01/2025	Wednesday	19	02:01:00	9.42
15		30/01/2025	Thursday	14	01:26:00	9.66
16		31/01/2025	Friday	13	01:40:00	7.74
17
Analysis

Cell Formulas
Range	Formula
B10:L16	B10	=FILTER(tblRunData,(tblRunData[Date]>=Analysis!B2)*(tblRunData[Date]<=EOMONTH(Analysis!B2,0)),"")
Dynamic array formulas.

 

[F0r3stH1k3r]

For year-to-date status: The workbook has a sheet titled Overall, which both pulls and provides data as a yearly summary. Having the year-to-date PR and Date it was attained on this sheet will work.

For month-by-month status: I can limit cells H5 and H6 to relate only to that month, though I would still need to figure out how to count Time and Date only if Distance is >=3.11

Regarding the date: I am certainly open to changing the current formulas as it means I will need to learn how FILTER works (learning is what I'm here for after all).

Here is how the date is currently determined: The current year is entered into cell C3 on the Overall sheet. On the month sheets, B2:K2 has ="month "&Overall!C3 and cell B10 has =DATE(Overall!C3,#,1) where # equals the number of the month. Cell B11 has =B10+1, and that formula is copied down to the last day of the month. The cells containing the date are then formatted as mmm - dd.