Issue trying to identify earliest date & time

sharky12345

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,422
Office Version
  1. 2016
Platform
  1. Windows
I'm trying to identify the earliest date & time with a single criteria and what's returned is incorrect.

The formula I'm using is this:

Excel Formula: 
=MIN(IF($A$2:$A5053=D2,$B$2:$B5053))

It's producing "00/01/1900 00:00"

The relevant cells are formatted as custom 'dd/mm/yyyy hh:mm' but no matter how I format the cells, the result is always incorrect.

I've attached a sample of the workbook, if anyone can help me understand how to resolve this please?

MrExcel.xlsx
ABCD
1Index NumberIndex TimeIndex NumberEarliest Index Time
2IND230000926020/04/2023 09:19IND230000926000/01/1900 00:00
3IND230000926011/04/2024 12:27IND2300011569
4IND230001156916/05/2023 14:50IND2300013377
5IND230001156910/04/2024 00:27IND2300016533
6IND230001156910/04/2024 00:29IND2300017703
7IND230001337706/06/2023 01:09IND2300025545
8IND230001337729/04/2024 13:09IND2400000003
9IND230001653311/07/2023 02:10IND2400001139
10IND230001653311/07/2023 20:30IND2400001938
11IND230001653310/01/2024 15:37IND2400002279
12IND230001653310/04/2024 16:17IND2400002425
13IND230001653310/04/2024 23:51IND2400002503
14IND230001770323/07/2023 16:47IND2400007799
15IND230001770323/07/2023 17:19IND2400008564
16IND230001770323/07/2023 23:29IND2400010159
17IND230001770309/04/2024 16:12IND2400010908
18IND230001770310/04/2024 00:21IND2400011060
19IND230002554520/10/2023 03:02IND2400011062
20IND230002554520/10/2023 03:24IND2400011063
21IND230002554520/10/2023 14:22IND2400011069
22IND230002554514/04/2024 11:49IND2400011081
23IND240000000301/01/2024 01:20IND2400011082
24IND240000000301/01/2024 04:54IND2400011084
25IND240000000301/04/2024 12:00IND2400011085
26IND240000113913/01/2024 00:49IND2400011087
27IND240000113912/04/2024 18:17IND2400011094
28IND240000193822/01/2024 12:13IND2400011096
29IND240000193819/04/2024 19:57IND2400011097
30IND240000227925/01/2024 21:25IND2400011100
31IND240000227908/04/2024 16:34IND2400011101
32IND240000242527/01/2024 18:29IND2400011102
33IND240000242524/04/2024 13:46IND2400011104
34IND240000250328/01/2024 13:08IND2400011105
35IND240000250328/01/2024 17:00IND2400011106
36IND240000250302/04/2024 16:50IND2400011107
37IND240000779914/03/2024 19:55IND2400011108
38IND240000779903/04/2024 10:51IND2400011111
39IND240000856408/04/2024 12:01IND2400011112
40IND240001015927/03/2024 11:47IND2400011117
41IND240001015927/03/2024 12:39IND2400011119
42IND240001015905/04/2024 11:58IND2400011122
43IND240001090831/03/2024 05:40IND2400011124
MREXCEL
Cell Formulas
RangeFormula
D2D2=MIN(IF($A$2:$A5053=C2,$B$2:$B5053))
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
That's not what I got. I only got that if there was no match.

This is what I got with your data set. 11/4/2024 12:27
 
Upvote 0
do you have a blank cell in column B , where the entry in the same row in A is the same as criteria
see highlighted cell in the list]
I had to recreate the dates as in UK and they came in as text

Book3
ABCD
1Index Number1/1/24 12:00Index NumberEarliest Index Time
2IND23000092601/2/24 11:00IND23000092601/0/00 0:00
3IND23000092601/3/24 10:00IND2300011569
4IND23000115691/4/24 9:00IND2300013377
5IND23000115691/5/24 8:00IND2300016533
6IND23000115691/6/24 7:00IND2300017703
7IND23000133771/7/24 6:00IND2300025545
8IND23000133771/8/24 5:00IND2400000003
9IND23000165331/9/24 4:00IND2400001139
10IND23000165331/10/24 3:00IND2400001938
11IND23000165331/11/24 2:00IND2400002279
12IND23000165331/12/24 1:00IND2400002425
13IND23000165331/13/24 0:00IND2400002503
14IND23000177031/13/24 23:00IND2400007799
15IND23000177031/14/24 22:00IND2400008564
16IND23000177031/15/24 21:00IND2400010159
17IND23000177031/16/24 20:00IND2400010908
18IND23000177031/17/24 19:00IND2400011060
19IND23000255451/18/24 18:00IND2400011062
20IND23000255451/19/24 17:00IND2400011063
21IND23000255451/20/24 16:00IND2400011069
22IND23000255451/21/24 15:00IND2400011081
23IND24000000031/22/24 14:00IND2400011082
24IND24000000031/23/24 13:00IND2400011084
25IND24000000031/24/24 12:00IND2400011085
26IND24000011391/25/24 11:00IND2400011087
27IND24000011391/26/24 10:00IND2400011094
28IND24000019381/27/24 9:00IND2400011096
29IND24000019381/28/24 8:00IND2400011097
30IND24000022791/29/24 7:00IND2400011100
31IND24000022791/30/24 6:00IND2400011101
32IND24000024251/31/24 5:00IND2400011102
33IND24000024252/1/24 4:00IND2400011104
34IND24000025032/2/24 3:00IND2400011105
35IND24000025032/3/24 2:00IND2400011106
36IND24000025032/4/24 1:00IND2400011107
37IND24000077992/5/24 0:00IND2400011108
38IND24000077992/5/24 23:00IND2400011111
39IND24000085642/6/24 22:00IND2400011112
40IND24000101592/7/24 21:00IND2400011117
41IND24000101592/8/24 20:00IND2400011119
42IND24000101592/9/24 19:00IND2400011122
43IND24000109082/9/24 19:00IND2400011124
44
45IND2300009260IND2300009260
Sheet1
Cell Formulas
RangeFormula
D2D2=MIN(IF($A$2:$A5053=C2,$B$2:$B5053))
 
Upvote 0
Hmm, that's the correct answer Scott - I don't know what I'm doing wrong 🤔
 
Upvote 0
etaf said:
do you have a blank cell in column B , where the entry in the same row in A is the same as criteria
see highlighted cell in the list]
I had to recreate the dates as in UK and they came in as text

Book3
ABCD
1Index Number1/1/24 12:00Index NumberEarliest Index Time
2IND23000092601/2/24 11:00IND23000092601/0/00 0:00
3IND23000092601/3/24 10:00IND2300011569
4IND23000115691/4/24 9:00IND2300013377
5IND23000115691/5/24 8:00IND2300016533
6IND23000115691/6/24 7:00IND2300017703
7IND23000133771/7/24 6:00IND2300025545
8IND23000133771/8/24 5:00IND2400000003
9IND23000165331/9/24 4:00IND2400001139
10IND23000165331/10/24 3:00IND2400001938
11IND23000165331/11/24 2:00IND2400002279
12IND23000165331/12/24 1:00IND2400002425
13IND23000165331/13/24 0:00IND2400002503
14IND23000177031/13/24 23:00IND2400007799
15IND23000177031/14/24 22:00IND2400008564
16IND23000177031/15/24 21:00IND2400010159
17IND23000177031/16/24 20:00IND2400010908
18IND23000177031/17/24 19:00IND2400011060
19IND23000255451/18/24 18:00IND2400011062
20IND23000255451/19/24 17:00IND2400011063
21IND23000255451/20/24 16:00IND2400011069
22IND23000255451/21/24 15:00IND2400011081
23IND24000000031/22/24 14:00IND2400011082
24IND24000000031/23/24 13:00IND2400011084
25IND24000000031/24/24 12:00IND2400011085
26IND24000011391/25/24 11:00IND2400011087
27IND24000011391/26/24 10:00IND2400011094
28IND24000019381/27/24 9:00IND2400011096
29IND24000019381/28/24 8:00IND2400011097
30IND24000022791/29/24 7:00IND2400011100
31IND24000022791/30/24 6:00IND2400011101
32IND24000024251/31/24 5:00IND2400011102
33IND24000024252/1/24 4:00IND2400011104
34IND24000025032/2/24 3:00IND2400011105
35IND24000025032/3/24 2:00IND2400011106
36IND24000025032/4/24 1:00IND2400011107
37IND24000077992/5/24 0:00IND2400011108
38IND24000077992/5/24 23:00IND2400011111
39IND24000085642/6/24 22:00IND2400011112
40IND24000101592/7/24 21:00IND2400011117
41IND24000101592/8/24 20:00IND2400011119
42IND24000101592/9/24 19:00IND2400011122
43IND24000109082/9/24 19:00IND2400011124
44
45IND2300009260IND2300009260
Sheet1
Cell Formulas
RangeFormula
D2D2=MIN(IF($A$2:$A5053=C2,$B$2:$B5053))
Click to expand...
I do have a blank cell Etaf yes!
 
Upvote 0
Ignore as 2016 does not have MINIFS

is column A the same as D2 with a blank cell

then that is seen as 0 and shows a date as you are getting
as that will be the min
so may need to use a MINIFS() and exclude blanks
 
Upvote 0
Perhaps:
=IF(ISNUMBER(MATCH(C2,$A$2:$A5053,0)),MIN(IF($A$2:$A5053=C2,$B$2:$B5053)),"")
 
Upvote 0
etaf said:
Ignore as 2016 does not have MINIFS

is column A the same as D2 with a blank cell

then that is seen as 0 and shows a date as you are getting
as that will be the min
so may need to use a MINIFS() and exclude blanks
Click to expand...
Nope - I've removed all of the blanks and there's no difference to the result.
 
Upvote 0
With 2016 you will need to confirm your formula with Ctrl Shift Enter, rather than just Enter.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
316
dshtesy
D
muhammad susanto
  • Solved
Extract Data to be Date Format (dd-mm-yyyy)
2
Replies
16
Views
147
muhammad susanto
muhammad susanto
H
  • Solved
Delete the last character of cells in column "K" if it is equal to "X" or "x"
Replies
8
Views
126
Fuji
F
E
  • Solved
How to scan a column and move adjacent date next to search cell?
Replies
3
Views
63
dreid1011
dreid1011
A
  • Question Question
Multiply an array based on date value entered.
Replies
8
Views
113
Fluff
Fluff

Forum statistics

Threads
1,223,879
Messages
6,175,148
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top