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

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

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

=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

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

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

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

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

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

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

See if this does anything...

Select an empty cell that has never been used and that the format has never
been changed. This can be any cell. You just want a cell that has *never*
been changed in any way.

Copy that empty cell: Right clickCopy
Select the range of cells that hold your times
Then: Right clickPaste SpecialAddOK

Sometimes this will convert TEXT numbers (numbers/dates/times) to numeric
numbers.

In Excel, dates/times are just numbers formatted to look like dates/times.
If that works it'll change your times to decimal numbers then you can format
in the Time style of your choice.

--
Biff
Microsoft Excel MVP


"Jules" wrote in message
...
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

Thanks Valko...I did this to no avail...still the same result for the time
column Charabeuh provided....

I used ASAP utility to check the format...date is number and time is number...

I tried using subtotals to get the latest time out for each room on each
day...the answer is 0:00.

This is the worst time I've had with figuring something out in Excel...I am
grateful to all who have tried to help...and will keep checking just to see
if anyone wants to keep trying.

Thanks,
--
Jules


"T. Valko" wrote:

See if this does anything...

Select an empty cell that has never been used and that the format has never
been changed. This can be any cell. You just want a cell that has *never*
been changed in any way.

Copy that empty cell: Right clickCopy
Select the range of cells that hold your times
Then: Right clickPaste SpecialAddOK

Sometimes this will convert TEXT numbers (numbers/dates/times) to numeric
numbers.

In Excel, dates/times are just numbers formatted to look like dates/times.
If that works it'll change your times to decimal numbers then you can format
in the Time style of your choice.

--
Biff
Microsoft Excel MVP


"Jules" wrote in message
...
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

What is the source of these time entries? Do they come from another
application? Imported from a website?

There is a macro at this location that will "clean" unseen characters like
leading/trailing spaces and those nasty char 160 html spaces from your data.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

--
Biff
Microsoft Excel MVP


"Jules" wrote in message
...
Thanks Valko...I did this to no avail...still the same result for the time
column Charabeuh provided....

I used ASAP utility to check the format...date is number and time is
number...

I tried using subtotals to get the latest time out for each room on each
day...the answer is 0:00.

This is the worst time I've had with figuring something out in Excel...I
am
grateful to all who have tried to help...and will keep checking just to
see
if anyone wants to keep trying.

Thanks,
--
Jules


"T. Valko" wrote:

See if this does anything...

Select an empty cell that has never been used and that the format has
never
been changed. This can be any cell. You just want a cell that has *never*
been changed in any way.

Copy that empty cell: Right clickCopy
Select the range of cells that hold your times
Then: Right clickPaste SpecialAddOK

Sometimes this will convert TEXT numbers (numbers/dates/times) to numeric
numbers.

In Excel, dates/times are just numbers formatted to look like
dates/times.
If that works it'll change your times to decimal numbers then you can
format
in the Time style of your choice.

--
Biff
Microsoft Excel MVP


"Jules" wrote in message
...
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

Okay...this is what I did...

I used =timevalue(cell) got the serial number and did a subtotal for Max on
that...then format cells and got the time....I don't know what the problem
was with the data...I've never had this happen before...so, I have the
information I needed and all is well.

Thank you, thank you, thank you, for all your help.
--
Jules


"T. Valko" wrote:

What is the source of these time entries? Do they come from another
application? Imported from a website?

There is a macro at this location that will "clean" unseen characters like
leading/trailing spaces and those nasty char 160 html spaces from your data.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

--
Biff
Microsoft Excel MVP


"Jules" wrote in message
...
Thanks Valko...I did this to no avail...still the same result for the time
column Charabeuh provided....

I used ASAP utility to check the format...date is number and time is
number...

I tried using subtotals to get the latest time out for each room on each
day...the answer is 0:00.

This is the worst time I've had with figuring something out in Excel...I
am
grateful to all who have tried to help...and will keep checking just to
see
if anyone wants to keep trying.

Thanks,
--
Jules


"T. Valko" wrote:

See if this does anything...

Select an empty cell that has never been used and that the format has
never
been changed. This can be any cell. You just want a cell that has *never*
been changed in any way.

Copy that empty cell: Right clickCopy
Select the range of cells that hold your times
Then: Right clickPaste SpecialAddOK

Sometimes this will convert TEXT numbers (numbers/dates/times) to numeric
numbers.

In Excel, dates/times are just numbers formatted to look like
dates/times.
If that works it'll change your times to decimal numbers then you can
format
in the Time style of your choice.

--
Biff
Microsoft Excel MVP


"Jules" wrote in message
...
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

Hello,

....How do I remove AltGR+0160?

One way to do that:
put the formula =CHAR(160) in an empty cell
The cell remain blank as if it was empty but is not.
copy this cell
select your columns of data (date,time,room)
Open the find/replace dialog box (CTRL+H)
in the text box FIND WHAT, paste what you have copied (look like a space)
leave the text box REPLACE WITH empty ( = replace the previous text by
nothing)
click on replace all.

i apologize for my english.




"Jules" a écrit dans le message de
...
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

OK, glad you got something to work!

However, I think you'd be better off fixing the actual problem rather than
just accommodating for it.

--
Biff
Microsoft Excel MVP


"Jules" wrote in message
...
Okay...this is what I did...

I used =timevalue(cell) got the serial number and did a subtotal for Max
on
that...then format cells and got the time....I don't know what the problem
was with the data...I've never had this happen before...so, I have the
information I needed and all is well.

Thank you, thank you, thank you, for all your help.
--
Jules


"T. Valko" wrote:

What is the source of these time entries? Do they come from another
application? Imported from a website?

There is a macro at this location that will "clean" unseen characters
like
leading/trailing spaces and those nasty char 160 html spaces from your
data.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

--
Biff
Microsoft Excel MVP


"Jules" wrote in message
...
Thanks Valko...I did this to no avail...still the same result for the
time
column Charabeuh provided....

I used ASAP utility to check the format...date is number and time is
number...

I tried using subtotals to get the latest time out for each room on
each
day...the answer is 0:00.

This is the worst time I've had with figuring something out in
Excel...I
am
grateful to all who have tried to help...and will keep checking just to
see
if anyone wants to keep trying.

Thanks,
--
Jules


"T. Valko" wrote:

See if this does anything...

Select an empty cell that has never been used and that the format has
never
been changed. This can be any cell. You just want a cell that has
*never*
been changed in any way.

Copy that empty cell: Right clickCopy
Select the range of cells that hold your times
Then: Right clickPaste SpecialAddOK

Sometimes this will convert TEXT numbers (numbers/dates/times) to
numeric
numbers.

In Excel, dates/times are just numbers formatted to look like
dates/times.
If that works it'll change your times to decimal numbers then you can
format
in the Time style of your choice.

--
Biff
Microsoft Excel MVP


"Jules" wrote in message
...
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