Thanks Charabeuh,

for the formula for time...the first cell gives me the number 2327 and it
decends from there....How do I remove AltGR+0160?

I have check each column for formating, by going to format cells and they
come up withe right format...but from the solutions I"m getting, it's not so
then?

Thank you for all you time.
--
Jules


"Charabeuh" wrote:

Hello,

Try these formula:

=SUM(--ISTEXT(B2:B2327)) (where B2:B2327 is column of date)
=SUM(--ISTEXT(I2:I2327)) (where I2:I2327 is column of hours)
These formula must be validate with Ctrl+Shift+Enter instead with Enter.
If at least one result is greater then zero == somme of your data are text
!
(and not dates and/or hours)

If any of your data are text and no space could be found, then perhaps the
character AltGR+0160 is there.
AltGR+0160 looks like a blank but is not. This character may appear when
data are imported or pasted from another application.




"Jules" a écrit dans le message de
...
Okay...i've done everything I can think of...

=MAX(IF((B2:B2327=K2)*(I2:I2327=L2),G2:G2327))
=COUNT(G2:G2327)=ROWS(2:2327)

Not sure what to do at this point.

I have both time columns formated to 37:30:30, there are not blanks in the
range and the count formula is coming out false...and the max time is
still
0:00:00....ugh....

Thanks for all the help...
--
Jules


"T. Valko" wrote:

If you're getting a result of 0 (or 0:00) then your times may not be true
Excel time values.

Try this...

If your times are in the range H5:H2330 and there are no empty cells then
this formula:

=COUNT(H5:H2330)=ROWS(5:2330)

Should return TRUE

--
Biff
Microsoft Excel MVP


"Jules" wrote in message
...
B = Date
P = Q Date
M = Room
H = PT Time

Still getting same result....ugh....

=MAX(IF((B5:B2330=P5)*(M5:M2330=Q5),H5:H2330))


--
Jules


"Jacob Skaria" wrote:

Try the below test..

Col A Col B Col C Col D Col E Col F
Date pt room Q.Date Qroom Formula
2-Jan-09 9:41:00 1 2-Jan-09 2 12:49:00
2-Jan-09 12:49:00 2
2-Jan-09 11:13:00 1
2-Jan-09 1:12:00 3
5-Jan-09 9:30:00 2
5-Jan-09 8:31:00 4
5-Jan-09 2:29:00 4
5-Jan-09 4:10:00 4

Q.Date is Query Date
Q.Room is Query Room
Formula used in F2 is (array entered)

=MAX(IF((A2:A10=D2)*(C2:C10=E2),B2:B10))

If this post helps click Yes
---------------
Jacob Skaria


"Jules" wrote:

=MAX(IF((B2:B2327=Q2)*(M2:M2327=R1),H2:H2327))

I left out the *, but still same result.
--
Jules


"Jules" wrote:

Hi Jacob, thank you so much for the help.

=MAX(IF((B2:B2327=Q2),M2:M2327=R1,H2:H2327))

Where r1 is the room number (my room numbers are in R1-w1, 6
rooms)

Returned 0:00:00, I entered it as an array formula. Both dates
are
in date
format.




--
Jules


"Jacob Skaria" wrote:

With date in D2 and room number in E2 ; try the below formula.
Please note
that this is an array formula. Within the cell in edit mode (F2)
paste this
formula and press Ctrl+Shift+Enter to apply this formula. If
successful in
'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

Col A and cell D2 should be in excel date format...Try and
feedback

=MAX(IF((A2:A10=D2)*(C2:C10=E2),B2:B10))

If this post helps click Yes
---------------
Jacob Skaria


"Jules" wrote:

I have a large data set, column A has many dates (there can be
many of the
same dates), B has pt out time, C has room number (1-6).

A
Date pt out room
1/02/09 9:41 1
1/02/09 12:49 2
1/02/09 11:13 1
1/02/09 13:12 3
1/05/09 9:30 2
1/05/09 8:31 4
1/05/09 14:29 4
1/05/09 16:10 4

I need to find the last case of the day in each room.

I tried countif, sumif, it's not working out.

If someone could help me I would be grateful!

Thanks so much,




--
Jules