1

I have a list of date and time rows, with multiple rows per day. For each unique date I want to get the min and max time values.

How would one go about doing this in Excel v10 (aka 2002)?

crackers
  • 153
  • 1
  • 3
  • 8
  • Possible duplicate of [How to find max and min in an alphanumeric data array in Excel?](https://stackoverflow.com/questions/46388285/how-to-find-max-and-min-in-an-alphanumeric-data-array-in-excel) – ashleedawg Sep 25 '17 at 06:48

2 Answers2

2

First, you could do this with Excel functions MIN(range) and MAX(range), but then you would have to construct a convoluted function to determine the range of rows with the given date.

Second, you could construct a pivot table... Using the date column for the rows, and having values of min time and max time, selecting from the time column and formatted as a time.

amaidment
  • 6,942
  • 5
  • 52
  • 88
  • Thanks. Yes, I was planning to use MIN() and MAX(), but wasn't sure of the most effective way to get the right range. Also tried PivotTable, but how can you get it to show MIN/MAX rather than a count? – crackers Jun 10 '12 at 18:12
  • Got it. Now just trying to get both MIN/MAX for each date to show in the same row rather than one below the other... – crackers Jun 10 '12 at 18:18
  • How can then those MIN/MAX be used to compute time interval between those two times? (And then monthly sum? And then some more?) – przemo_li Jan 22 '16 at 07:12
  • @przemo_li - it sounds like you have a specific question of your own in mind, in which case, may i suggest that you post it as a new question? – amaidment Jan 22 '16 at 12:15
2

You may filter the data in-place by adding subtotals. First add subtotals with minimum(value), then again with maximum(value) without removing the previous subtotals. Then hide 3-d level rows to show only min and max values for each date.

If you have the list of unique dates, you can get min/max value for any particular date with formula =MAX(IF(CellWithTheDate=Date_Range,Value_Range,"")) entered as an array formula

The list of unique dates may be produced by extended autofilter on date column (there's a checkbox for unique values) either in-place or by making a copy.

panda-34
  • 4,089
  • 20
  • 25