# I Want the minimum date based on cell value

#### Jaffabfc

##### Board Regular
Hi,

I am wanting to get the minimum date from sheet "Orders" column "F" based on cell a2 cross referenced with row "Orders" column "A"

So column A in orders is numbers 1 - 10 multiple times
Column F in Orders is multiple different dates (some the same date)
so in another sheet cell I2 i am wanting the earliest date from the value of cell A2 with the above data to bring back earliest date

i currently have a formula that shows if its within 5 days or less of todays date put today +14, but i need the exact information now.

this is the formula ive been using.

Link is the sheet i am doing the formula on

thanks

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### AlanY

##### Well-known Member
either one should work

Book1
ABCDEFGHI
1520/09/2020
2230/08/202030/08/2020
3628/08/202030/08/2020
4921/09/2020
5604/09/2020
6813/09/2020
7325/08/2020
8707/09/2020
9925/08/2020
10213/09/2020
11219/09/2020
12830/08/2020
13618/09/2020
14208/09/2020
15805/09/2020
16319/09/2020
17513/09/2020
18812/09/2020
19420/09/2020
20115/09/2020
Sheet2
Cell Formulas
RangeFormula
I2I2=MINIFS(F1:F20,A1:A20,A2)
I3I3=MIN(IF(A1:A20=A2,F1:F20))
Press CTRL+SHIFT+ENTER to enter array formulas.

#### Jaffabfc

##### Board Regular
Hi,

Thanks for the update above, it seems to work above but it isnt working on mine.
I am getting the error 00/01/1900.
Also what if the array of data changes so a2:10 changes to a2:a500, how do i make it pick this up?

#### AlanY

##### Well-known Member
Hi,

Thanks for the update above, it seems to work above but it isnt working on mine.
I am getting the error 00/01/1900.

that probably due to the cell formatted as date instead of no

Also what if the array of data changes so a2:10 changes to a2:a500, how do i make it pick this up?

just extend the range

#### Jaffabfc

##### Board Regular

is there no way of the range extending automatically?
i can be updating this approx every hour so changing this hourly is quite a pain.

also the format of the cell i have the formula in is date
the column a and cells a2 etc are all as general format.

#### AlanY

##### Well-known Member
is there no way of the range extending automatically?
i can be updating this approx every hour so changing this hourly is quite a pain.

also the format of the cell i have the formula in is date
the column a and cells a2 etc are all as general format.

you can set it up to the largest range you're expecting, setting it too large than necessary will slow it down, that's all

Replies
6
Views
89
Replies
3
Views
79
Replies
0
Views
204
Replies
2
Views
54
Replies
4
Views
90

1,140,924
Messages
5,703,182
Members
421,280
Latest member
Jaycee01

### 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.

### Which adblocker are you using?

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

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