Min & Max Date Returning 00/01/1900

Good Afternoon Excel team,

I am hoping you are able to help me.

Attached are two tabs:

Data : Raw Data
Analysis : On the Analysis Tab i am trying to determine when the First Change (Min Date) And Last Change (Max Date)

For Example. if i isolate row 17 "Ownership Structure" I would expect the following outcomes
First change : 06/07/2012
Last Change : 23/03/2013

Networking Days is the count between both dates so in this example it be 186

However I am returning the data value of "00/01/1900".

I believe this is occurring because dates in column B on the Data Tab isn't in the correct Format.

Is someone able to review the format and advise where i am going wrong ?

Looking forward to your response

Try this:

=MIN(IF(FieldEvents=A17,Data!$B$2:$B$13))

and this:

=MAX(IF(FieldEvents=A17,Data!$B$2:$B$13))

... both confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

1. use this array formula instead:

=IFERROR(1/(1/MIN(IF(FieldEvents=A2,Data!B:B))),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

2. Change away from using whole column references. They are SOOOO SLOW .

hi there. you are getting it should be an array formula but you did not use the CTRL + SHIFT + ENTER key. you also shouldn't use the entire column as that slows down the file a lot when using array formulas. convert your data in Data sheet A1:E13 first. click on A1. press CTRL + T.

then in Analysis worksheet, cell C17:
=MIN(IF(Table1[Field / Event]=A17,Table1[Edit Date]))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

why is it 6-Jul and not 5-Jul by the way?

Indeed, I have replaced the Named Range with two named ranges (currently set to go down to row 100) which will automatically adjust to include only the required rows:

The array formula now looks like:

=IFERROR(1/(1/MIN(IF(FieldEvents=$A2,dates))),"")

and

=IFERROR(1/(1/MAX(IF(FieldEvents=$A2,dates))),"")

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

You're welcome!