Is there a way to sort a column on just the date portion of a day+time field?

[JenniferMurphy]

Consider this table containing 7 items that each have a Last Used field and a Rating field.

Sorting.xlsx
	C	D	E
4	Item	Last Used	Rating
5	A	8/28/23 14:33	91
6	B	8/27/23 18:25	81
7	C	8/28/23 08:21	89
8	D	8/27/23 09:35	78
9	E	8/28/23 17:53	83
10	F	8/27/23 11:22	95
11	G	8/28/23 07:02	77
Dates

I would like to sort the table by Last Used (oldest to newest) and then by Rating (high to low). If I do that, I get this:

Sorting.xlsx
	C	D	E
4	Item	Last Used	Rating
5	D	8/27/23 09:35	78
6	F	8/27/23 11:22	95
7	B	8/27/23 18:25	81
8	G	8/28/23 07:02	77
9	C	8/28/23 08:21	89
10	A	8/28/23 14:33	91
11	E	8/28/23 17:53	83
Dates

In this sort, the Rating field is irrelevant because no two Last Used values are identical. There are many that happened on the same date, but at different times. I only want the sort to take into account the date portion of the Last Used field.

I can get the result I want by adding a helper column with just the date portion of the Last Used field.

Sorting.xlsx
	C	D	E	F
4	Item	Last Used	Rating	Date
5	F	8/27/23 11:22	95	8/27/23 00:00
6	B	8/27/23 18:25	81	8/27/23 00:00
7	D	8/27/23 09:35	78	8/27/23 00:00
8	A	8/28/23 14:33	91	8/28/23 00:00
9	C	8/28/23 08:21	89	8/28/23 00:00
10	E	8/28/23 17:53	83	8/28/23 00:00
11	G	8/28/23 07:02	77	8/28/23 00:00
Dates

Cell Formulas
Range	Formula
F5:F11	F5	=ROUNDDOWN([@[Last Used]],0)

Is there a way I can sort this table and get this result without the use of a helper column?

 

[johnnyL]

You didn't mention if you were looking for a formula solution, VBA solution, either or.

 

[alansidman]

A Power Query solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Last Used", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Last Used", Order.Ascending}, {"Rating", Order.Descending}})
in
    #"Sorted Rows"

 

[JenniferMurphy]

You didn't mention if you were looking for a formula solution, VBA solution, either or.

I was hoping for a simple expression I could put in a cell. Failing that, VBA code.

 

[JenniferMurphy]

A Power Query solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Last Used", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Last Used", Order.Ascending}, {"Rating", Order.Descending}})
in
    #"Sorted Rows"

I have been meaning to read up on Power Queries, so this is the nudge I need. I am assuming that this code goes in a macro?

Thanks

 

[bebo021999]

If you don't mind employing a helper column:

Book2
	C	D	E	F
4	Item	Last Used	Rating	Helper
5	A	28 Aug 23 14:33	91	45,165.9999090
6	B	27 Aug 23 18:25	81	45,164.9999190
7	C	28 Aug 23 08:21	89	45,165.9999110
8	D	27 Aug 23 09:35	78	45,164.9999220
9	E	28 Aug 23 17:53	83	45,165.9999170
10	F	27 Aug 23 11:22	95	45,164.9999050
11	G	28 Aug 23 07:02	77	45,165.9999230
Sheet1

Cell Formulas
Range	Formula
F5:F11	F5	=INT(D5)-E5*10^-6

Then sort column F (Helper)

Book2
	C	D	E	F
4	Item	Last Used	Rating	Helper
5	F	27 Aug 23 11:22	95	45,164.9999050
6	B	27 Aug 23 18:25	81	45,164.9999190
7	D	27 Aug 23 09:35	78	45,164.9999220
8	A	28 Aug 23 14:33	91	45,165.9999090
9	C	28 Aug 23 08:21	89	45,165.9999110
10	E	28 Aug 23 17:53	83	45,165.9999170
11	G	28 Aug 23 07:02	77	45,165.9999230
Sheet1

Cell Formulas
Range	Formula
F5:F11	F5	=INT(D5)-E5*10^-6

 

[JenniferMurphy]

If you don't mind employing a helper column:

Uh, er, . . .

I mentioned a helper column in my original post. I would prefer to not have one.

 

[bebo021999]

I mentioned a helper column in my original post. I would prefer to not have one.

Oh, I didn't notice that you didn't want a helper column.
I wonder if a piece of VBA code would intrigue you?

 

[MARK858]

I am assuming that this code goes in a macro?

When you pull a table into Power Query a new window opens, Click the Advanced Editor icon and another window will open and the code goes into there.

 

[JenniferMurphy]

When you pull a table into Power Query a new window opens, Click the Advanced Editor icon and another window will open and the code goes into there.

Thanks for that additional detail. I will try to get up to speed on Power Queries.