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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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
 
Upvote 0
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
 
Upvote 0
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"
 
Upvote 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!,"")

>> ""
 
Upvote 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!
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

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
Back
Top