First and Last date capture

[Ashhu]

Hi,
Need help to get this issue solved.
I have name and respective date in A and B column.
I need to fetch start date (minimum) and End date (maximum) based on the name.
See attached copy.

 

[Khalid NGO]

Hi Ashwin,

Try this for Start Date:
=MIN(IF($A$2:$A$22=$E5,$B$2:$B$22))

and this for End Date:
=MAX(IF($A$2:$A$22=$E5,$B$2:$B$22))

both are array formula, enter with Ctrl+Shift+Enter

Regards,

 

[Ashhu]

Clean,
Thanks Khalid.
actually i am trying to help here,
http://forum.chandoo.org/threads/help-with-date-time-formula-please.29182/

But not sure how to fix weekend issue. I will reply to thread and loop you for check.

Regards

 

[Khalid NGO]

Hi Ashwin, and all,

I was looking for a non-array solution for the above, and found here:

Non array version of MAX(IF(...
=SUMPRODUCT(MAX(($A$2:$A$22=$E5)*$B$2:$B$22))

Non array versions of MIN(IF...
=MIN(INDEX(--SUBSTITUTE(--($A$2:$A$22=E5),0,10^7)*$B$2:$B$22,))

=MIN(INDEX((($A$2:$A$22=$E5)*$B$2:$B$22)+(($A$2:$A$22<>$E5)*1E+100),0))

What the heck is 1E+100

Regards,

 

[Ashhu]

Hi Ashwin, and all,

I was looking for a non-array solution for the above, and found here:

Non array version of MAX(IF(...
=SUMPRODUCT(MAX(($A$2:$A$22=$E5)*$B$2:$B$22))

Non array versions of MIN(IF...
=MIN(INDEX(--SUBSTITUTE(--($A$2:$A$22=E5),0,10^7)*$B$2:$B$22,))
=MIN(INDEX((($A$2:$A$22=$E5)*$B$2:$B$22)+(($A$2:$A$22<>$E5)*1E+100),0))

What the heck is 1E+100

Regards,

 

[NARAYANK991]

Hi Ashwin, and all,

I was looking for a non-array solution for the above, and found here:

Non array version of MAX(IF(...
=SUMPRODUCT(MAX(($A$2:$A$22=$E5)*$B$2:$B$22))

Non array versions of MIN(IF...
=MIN(INDEX(--SUBSTITUTE(--($A$2:$A$22=E5),0,10^7)*$B$2:$B$22,))
=MIN(INDEX((($A$2:$A$22=$E5)*$B$2:$B$22)+(($A$2:$A$22<>$E5)*1E+100),0))

What the heck is 1E+100

Regards,

Hi Khalid ,

It's nothing but a very big number !

($A$2:$A$22<>$E5) resolves to the Boolean values TRUE and FALSE as the only two possibilities ; when an arithmetic operation is performed on these Boolean values , their values are used as 1 and 0 , which means an array of TRUE and FALSE values , when multiplied by , let us say BIGNUM , will result in an array of values BIGNUM and 0.

Narayan

 

[John Jairo V]

Hi to all!

What about:
Start Date : =AGGREGATE(15,6,$B$2:$B$22/($A$2:$A$22=$E5),1)
End Date : =AGGREGATE(14,6,$B$2:$B$22/($A$2:$A$22=$E5),1)

1E+100 is a scientific notation, equals to 1 * 10^100. As @NARAYANK991 said, is a big number. I'd prefer the biggest: 9,9E+307 in formulas. Blessings!

 

[Ashhu]

John
Does above formula works in 2007 version, i am ending up in #Name? error

 

[NARAYANK991]

Hi Ashwin ,

The AGGREGATE function is available only from Excel 2010 onwards.

Narayan

 

[Khalid NGO]

Hi John and Narayan Sir, thanks for the clarification.

The more I learn excel, more I realize how less I know.

Thankfully, I have the forum like this, and guys like you.

Regards,

 

[Marc L]

Hi !

The more I learn excel, more I realize how less I know.

The same … The only thing I know is that I do not know all !
​