exceldatetimeranking
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
Excel - Ranking of date & time, rank by day
Please see list of date and times in cells:
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)
