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

[TABLE="width: 500"]
<tbody>[TR]
[TD]date[/TD]
[TD]time[/TD]
[/TR]
[TR]
[TD]01/01/2019[/TD]
[TD]18:45[/TD]
[/TR]
[TR]
[TD]01/01/2019[/TD]
[TD]18:45[/TD]
[/TR]
[TR]
[TD]01/01/2019[/TD]
[TD]18:45[/TD]
[/TR]
[TR]
[TD]01/01/2019[/TD]
[TD]18:45[/TD]
[/TR]
[TR]
[TD]01/01/2019[/TD]
[TD]18:45[/TD]
[/TR]
[TR]
[TD]02/01/2019[/TD]
[TD]08:23[/TD]
[/TR]
[TR]
[TD]02/01/2019[/TD]
[TD]08:23[/TD]
[/TR]
[TR]
[TD]03/01/2019[/TD]
[TD]08:24[/TD]
[/TR]
[TR]
[TD]03/01/2019[/TD]
[TD]23:24[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]date[/td][td=bgcolor:#5B9BD5]time[/td][td][/td][td=bgcolor:#70AD47]date[/td][td=bgcolor:#70AD47]time[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

01/01/2019​

[/td][td=bgcolor:#DDEBF7]

18:45​

[/td][td][/td][td=bgcolor:#E2EFDA]

01/01/2019​

[/td][td=bgcolor:#E2EFDA]

18:45:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

01/01/2019​

[/td][td]

18:45​

[/td][td][/td][td]

02/01/2019​

[/td][td]

08:23:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

01/01/2019​

[/td][td=bgcolor:#DDEBF7]

18:45​

[/td][td][/td][td=bgcolor:#E2EFDA]

03/01/2019​

[/td][td=bgcolor:#E2EFDA]

23:24:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

01/01/2019​

[/td][td]

18:45​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

01/01/2019​

[/td][td=bgcolor:#DDEBF7]

18:45​

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

[tr=bgcolor:#FFFFFF][td]

02/01/2019​

[/td][td]

08:23​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

02/01/2019​

[/td][td=bgcolor:#DDEBF7]

08:23​

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

[tr=bgcolor:#FFFFFF][td]

03/01/2019​

[/td][td]

08:24​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

03/01/2019​

[/td][td=bgcolor:#DDEBF7]

23:24​

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

[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]