Return the Earliest Date

Impos2004

New Member
Joined
Jun 10, 2013
Messages
15
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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Fishboy

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
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
 

Impos2004

New Member
Joined
Jun 10, 2013
Messages
15
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
 

Fishboy

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
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"
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203

ADVERTISEMENT

Control+shift+enter, not just enter:

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

Impos2004

New Member
Joined
Jun 10, 2013
Messages
15

ADVERTISEMENT

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
[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!,"")

>> ""
 

Fishboy

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
[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!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,450
Members
417,025
Latest member
MusterDuster

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
Top