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
 

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,138
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
That's awesome, thanks so much for your help! Problem Solved! :)
 

Aladin Akyurek

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

Forum statistics

Threads
1,082,632
Messages
5,366,678
Members
400,913
Latest member
SarahMS1

Some videos you may like

This Week's Hot Topics

Top