Time and the Max Function

[Jules]

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,

 

[Jacob Skaria]

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

 

[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]

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

I left out the *, but still same result.

 

[Jacob Skaria]

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

 

[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))

 

[T. Valko]

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

 

[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...

 

[Charabeuh]

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]

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.

 

[T. Valko]

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 click>Copy
Select the range of cells that hold your times
Then: Right click>Paste Special>Add>OK

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.

 

[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,

 

[T. Valko]

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

 

[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.

 

[Charabeuh]

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.

 

[T. Valko]

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.