How to get the earliest time by row

[JohnCollins]

Can anyone provide me with a formula to obtain the "Earliest Response Time" for each row when "Response A" or "Response B" = "Responded". If both do not match then the earliest response time should remain blank as per the entries shown in the table below:

Name Response A Date A Response B Date B Earliest Response Time
John Responded 15/07/2019 08:00 Responded 15/07/2019 08:30 15/07/2019 08:00
Fred Responded 15/07/2019 08:30 Delivered 15/07/2019 09:30 15/07/2019 07:30
Sarah Responded 15/07/2019 07:30 Responded 15/07/2019 08:30 15/07/2019 07:30
Susan Responded 15/07/2019 09:30 Delivered 15/07/2019 09:30 15/07/2019 09:30
David Delivered 15/07/2019 07:30 Delivered 15/07/2019 09:30

Column Responses 15/07/2019 07:30 15/07/2019 08:30

Note: I'm able to obtain the column responses by using the appropriate formula: "=MINIFS(C2:C5,B2:B5,"Responded")"

Many Thanks

 

[Special-K99]

Fred Responded 15/07/2019 08:30 Delivered 15/07/2019 09:30 15/07/2019 07:30

Why is the result of this 07:30 ?
There is no 07:30 in that row
The earliest is 08:30

 

[JohnCollins]

It was a typeO when I entered the original posting; good spot as it should be 08:30

 

[Marcelo Branco]

Try


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Name​

[/td][td]

ResponseA​

[/td][td]

DateA​

[/td][td]

ResponseB​

[/td][td]

DateB​

[/td][td]

EarliestResponseTime​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

John​

[/td][td]

Responded​

[/td][td]

15/07/2019 08:00​

[/td][td]

Responded​

[/td][td]

15/07/2019 08:30​

[/td][td]

15/07/2019 08:00​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Fred​

[/td][td]

Responded​

[/td][td]

15/07/2019 08:30​

[/td][td]

Delivered​

[/td][td]

15/07/2019 09:30​

[/td][td]

15/07/2019 08:30​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Sarah​

[/td][td]

Responded​

[/td][td]

15/07/2019 07:30​

[/td][td]

Responded​

[/td][td]

15/07/2019 08:30​

[/td][td]

15/07/2019 07:30​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Susan​

[/td][td]

Responded​

[/td][td]

15/07/2019 09:30​

[/td][td]

Delivered​

[/td][td]

15/07/2019 09:30​

[/td][td]

15/07/2019 09:30​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

David​

[/td][td]

Delivered​

[/td][td]

15/07/2019 07:30​

[/td][td]

Delivered​

[/td][td]

15/07/2019 09:30​

[/td][td][/td][/tr]
[/table]

F2 copied down
=IFERROR(AGGREGATE(15,6,C2:E2/(B2:D2="Responded"),1),"")

Format as dd/mm/yyyy hh:mm

M.

 

[JohnCollins]

Thanks for the response, the solution works great and I would never have thought of using "Aggregate". Just for clarity can you advise what the last few entries do following the word "Responded") within the formula
?

 

[Marcelo Branco]

AGGREGATE(15 = SMALL and the 1 (last parameter) indicates the smallest value (equivalent to minimum). Take at look at the help about this function.

IFERROR wrapping the AGGREGATE function is used to return an empty string, if there are no values satisfying the condition B2:D2 = "Responded".

M.