max time for date

[rsj88]

Hi guys i have the below table:

if i have a date in A1 i want to get the max time for that date.

Thanks

date	time
01/01/2019	18:45
01/01/2019	18:45
01/01/2019	18:45
01/01/2019	18:45
01/01/2019	18:45
02/01/2019	08:23
02/01/2019	08:23
03/01/2019	08:24
03/01/2019	23:24

<tbody>
</tbody>

<tbody>
</tbody>

 

[FormR]

Hi, if you have a newer version of Excel take a look at MAXIFS(), otherwise here is one option.

Excel 2013/2016
	A	B
1	03/01/2019	23:24
2
3	date	time
4	01/01/2019	18:45
5	01/01/2019	18:45
6	01/01/2019	18:45
7	01/01/2019	18:45
8	01/01/2019	18:45
9	02/01/2019	08:23
10	02/01/2019	08:23
11	03/01/2019	08:24
12	03/01/2019	23:24
Sheet1

Cell Formulas
Range	Formula
B1	=AGGREGATE(14,6,B4:B12/(A4:A12=A1),1)

 

[sandy666]

or

date	time		date	time
01/01/2019​	18:45​		01/01/2019​	18:45:00​
01/01/2019​	18:45​		02/01/2019​	08:23:00​
01/01/2019​	18:45​		03/01/2019​	23:24:00​
01/01/2019​	18:45​
01/01/2019​	18:45​
02/01/2019​	08:23​
02/01/2019​	08:23​
03/01/2019​	08:24​
03/01/2019​	23:24​

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"date", type date}, {"time", type time}}),
    Group = Table.Group(Type, {"date"}, {{"time", each List.Max([time]), type time}})
in
    Group[/SIZE]