PDA

View Full Version : want to find max time & min time



amit_nalawad
01-31-2008, 08:56 AM
13:00:02
16:09:04
16:09:28
16:29:25
16:29:48
19:10:33
19:33:24
20:06:54
20:09:01
22:04:32
22:06:54
0:16:43
0:17:13
0:30:14
0:30:54
0:52:26
I have this list of times. I want to find max and min from this list. max should come 0:52:26 and min should come 13:00:02.

Bob Phillips
01-31-2008, 09:04 AM
You will have to explain how max is 0:52:26 when there are times much later in the list.

amit_nalawad
01-31-2008, 09:14 AM
Hi xld,

Acutaly 0:52:26 is 12:52:26AM.

Bob Phillips
01-31-2008, 09:51 AM
That is still much smaller than 13:00 whixch is 1 PM

RonMcK
01-31-2008, 11:11 AM
I have this list of times. I want to find max and min from this list. max should come 0:52:26 and min should come 13:00:02.

Your values are the first and last ones in the list but are not the min and max of the list.

Using the MIN() and MAX() functions on your range gives:
MIN = 00:16:43
MAX = 22:06:54

Sorting the time entries in ascending order will help you see why these are the correct values.

Ron

amit_nalawad
02-01-2008, 04:37 AM
Here I have attached one file. Which shows entry and exit times. I want to find the first entry and last exit on that day.

RonMcK
02-01-2008, 07:18 AM
Amit,

Please try the upload, again. The system seems to have thwarted you effort.

Ron

Bob Phillips
02-01-2008, 08:27 AM
It would have helped if you had mentioned the date and status criteria at the start!

=MIN(IF(($A$2:$A$37=--"2007-12-04")*($B$2:$B$37="Entry"),$C$2:$C$37))

=MAX(IF(($A$2:$A$37=--"2007-12-04")*($B$2:$B$37="Exit"),$C$2:$C$37))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.When editing the formula, it must again be array-entered.Note that you cannot use a whole column in array formulae (prior to excel 2007), but must use an explicit range.

amit_nalawad
02-01-2008, 02:10 PM
Thank you very much xld

Aussiebear
02-01-2008, 06:50 PM
and if the period is greater than 24 hours?