exceldatetimeranking

Excel - Ranking of date & time, rank by day

Please see list of date and times in cells:

List of dates and times


I would like to find the ranking of the cell's date and time according to what day it falls on.

So in this example "14/12/2023 09:06" would rank 1, "14/12/2023 13:04" would rank 2. "15/12/2023 08:59" would rank 1, "15/12/2023 13:28" would rank 2. And finally, "19/12/2023 10:49" would rank 1, "19/12/2023 13:03" would rank 2. Any ideas on how to retrieve these rankings based on the day?
Thanks in advance
Solution

  • You may try-

    =XMATCH(A1,SORT(FILTER($A$1:$A$6,($A$1:$A$6>=INT(A1)+TIME(0,0,1))*($A$1:$A$6<=INT(A1)+TIME(23,59,59)))),1)

    enter image description here