min value with conditions

[Retiger]

I

ve a table with the entrance and exit hours of the guys in my department.  But they

re a lot of entrances and exits in the same day !! I want to add a formula to detect the first and last hour each day.


The table is as follows:

name;date;hour;


I`ve tought something as


=MAX((([Date]=[@Date])*([Name]=[@Name])*([hour])))=[@[hour]]


it works !! I get the last hour each day... but the first hour ?? difficult, i dont get it... please help !!

 

[SirJB7]

Hi, Retiger!

Tried same formula with MIN instead of MAX?

Regards!

 

[Retiger]

Hi SirJB7... Ive tried... but the problem is than the min = 0, there are a lot of zeros in the array... i want the first result after 0. SMALL also doesnt work.


but thanks a lot !

 

[SirJB7]

Hi, Retiger!

I supposed you did but I had to ask. And if you add a last multiplication entering the condition of hour greater than zero?

Regards!

 

[Retiger]

I dont see how... the point is that hour is always greater than zero... the array multiplication gives a result like "0/0/0/0/0/hour/0/0/0/0/hour/... "... i want to select the mininum value or hour.

 

[oldchippy]

Hi,


Try something like this to find the smallest excluding zero's


=SMALL(A1:A10,1+COUNTIF(A1:A10,0))

 

[Retiger]

It works... thank a lot Oldchippy !!

 

[prasaddn]

Hi, What is the final formula?


The oldchippy formula just gives the earliest log in time, but how will it check for the given name and date Criterias?


Regards,

Prasad DN

 

[prasaddn]

Well, I got the answer after along permutation and combinations


=SMALL((A2:A37=H8)*(B2:B37=I8)*(C2:C37),(1+ROWS(A2:A37)-SUMPRODUCT((A2:A37=H8)*(B2:B37=I8))))


Entered as CSE.


If trick was to find out how many 0s you get, so that least of true values can be found.


Regards,

Prasad DN

Edit: I assume names are in Col A, Dates are in Col B and entry time is in Col C. And input of name is given in H8, and input date in I8