Return Min/Max of One Column Based on Min/Max of Another Column, Without Helper Column

[DerekK]

	A	B
1	Time	Date
2	10:00	28/11/2023
3	11:00	28/11/2023
4	12:00	27/11/2023
5	07:00	29/11/2023
6	08:00	29/11/2023
7	09:00	29/11/2023

How do I return, e.g., "07:00 29/11/2023?" That is, the minimum time from the maximum date, with a formula but without a helper column?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[awoohaw]

This may work for you. You will probably need to enter the forumula with the CSE (CNTL-SHFT-ENTR) keystroke.

Mr Excel Questions 73.xlsm
	A	B	C
1	Time	Date
2	10:00	28/11/2023	07:00 29/11/2023
3	11:00	28/11/2023
4	12:00	27/11/2023
5	07:00	29/11/2023
6	08:00	29/11/2023
7	09:00	29/11/2023
Derekk

Cell Formulas
Range	Formula
C2	C2	=(MAX($B$2:$B$7))+1/MAX(IFERROR(1/(($A$2:$A$7)*(MAX($B$2:$B$7)=($B$2:$B$7))),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Two other options depending on what version you have

Fluff.xlsm
	A	B	C
1	Time	Date
2	10:00:00	28/11/2023	29/11/2023 07:00
3	11:00:00	28/11/2023	29/11/2023 07:00
4	12:00:00	27/11/2023
5	07:00:00	29/11/2023
6	08:00:00	29/11/2023
7	09:00:00	29/11/2023
Data

Cell Formulas
Range	Formula
C2	C2	=MAX(B2:B10)+MINIFS(A:A,B:B,MAX(B2:B10))
C3	C3	=MAX(B2:B10)+MIN(IF(B2:B10=MAX(B2:B10),A2:A10))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[DerekK]

Both of these work -- I tried to mark both as solution -- thank you very much!

 

[Fluff]

Glad we could help, but what version of Excel are you using? It helps us to help you.

 

[DerekK]

Glad we could help, but what version of Excel are you using? It helps us to help you.

I appreciate it but I prefer to go with the "oldest" acceptable method, since I don't want to worry so much about whether it will work on a given computer / version.

 

[Fluff]

In that case select that in your profile, it saves members wasting their time providing a formula that will not work in older versions.

 

[awoohaw]

Happy to help. Best wishes.