# Return the Earliest Date

#### Impos2004

##### New Member
Hi,

I'd really appreciate some help with, what will probably prove to be a pretty simple problem.

I have a list of dates in column A, next to a list of statuses in column B, as follows....

01/01/2012 New
01/01/2013 Open
05/05/1978 Closed
22/04/2015 New

The dates are stored as dates, not text.

I would like to write a formula that will return the earliest date that corresponds with a value of "New". Please can someone help?

Thanks

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Hi,

I'd really appreciate some help with, what will probably prove to be a pretty simple problem.

I have a list of dates in column A, next to a list of statuses in column B, as follows....

01/01/2012 New
01/01/2013 Open
05/05/1978 Closed
22/04/2015 New

The dates are stored as dates, not text.

I would like to write a formula that will return the earliest date that corresponds with a value of "New". Please can someone help?

Thanks
Hi Impos2004,

The following formula should do the trick. This formula needs to be applied using CTRL+SHIFT+ENTER, not just ENTER.

=MIN(IF(B1:B4="New",A1:A4))

The end result will look like this in the formula bar:

{=MIN(IF(B1:B4="New",A1:A4))}

Do not try to add the curly braces manually as this will not work, you MUST enter the formula using CTRL+SHIFT+ENTER, not just ENTER

That works really well, thank you.

If I can ask, I also need a version of this that will work where there happens to be no data present too (the formula will eventually be calling in data from multiple workbooks, some of which may not be populated yet, or potentially all of the statuses will be "Closed" rather than "New").

For example, if there are no dates or "New" statuses available, the formula will return a value of "No Data", rather than a date of 00/01/1900.

Many thanks

That works really well, thank you.

If I can ask, I also need a version of this that will work where there happens to be no data present too (the formula will eventually be calling in data from multiple workbooks, some of which may not be populated yet, or potentially all of the statuses will be "Closed" rather than "New").

For example, if there are no dates or "New" statuses available, the formula will return a value of "No Data", rather than a date of 00/01/1900.

Many thanks
Hmm, you could try this instead (also entered with CTRL+SHIFT+ENTER)

=IF(AND(COUNTA(A1:A4)>0,COUNTIF(B1:B4,"New")>0),MIN(IF(B1:B4="New",A1:A4)),"No Data")

So only if there is data in the date column AND there is at least one instance of New, do the MIN IF formula, otherwise show "No Data"

Control+shift+enter, not just enter:

=IFERROR(1/(1/MIN(IF(B1:B4="New",A1:A4))),"")

Control+shift+enter, not just enter:

=IFERROR(1/(1/MIN(IF(B1:B4="New",A1:A4))),"")
Wow, that is impressive. I wish I understood how it works!

That's awesome, thanks so much for your help! Problem Solved!

That's awesome, thanks so much for your help! Problem Solved!
Happy to help, but I got blown out of the water by Aladin's swanky formula

[A]

IFERROR(value, value_if_error)

IFERROR(1/(1/x),"")

when x = 4

IFERROR(1/(1/4),"")

>>

IFERROR(1/(0.25),"")

>> 4 (x survives as is)

when x = 0

IFERROR(1/(1/0),"")

>>

IFERROR(#DIV/0!,"")

>> ""

[A]

IFERROR(value, value_if_error)

IFERROR(1/(1/x),"")

when x = 4

IFERROR(1/(1/4),"")

>>

IFERROR(1/(0.25),"")

>> 4 (x survives as is)

when x = 0

IFERROR(1/(1/0),"")

>>

IFERROR(#DIV/0!,"")

>> ""

...

I am more lost now than before when I thought it was pure witchcraft!

Replies
4
Views
284
Replies
15
Views
217
Replies
2
Views
215
Replies
4
Views
175
Replies
7
Views
140

1,218,595
Messages
6,143,386
Members
450,484
Latest member
ChrisMac1

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