Return the Earliest Date

[Impos2004]

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]

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]

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]

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]

Control+shift+enter, not just enter:

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

 

[Fishboy]

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!

 

[Impos2004]

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

 

[Fishboy]

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

 

[Aladin Akyurek]

[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]

[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!