First/Last Date in a Range

[normb4]

I have a stubborn issue that has bugged me for a long time in Excel.

I have a report that renders a long string of date and times in a column. What I need to do is to locate the first event and last event for each day in that range.

Example: the data in columns looks something like this

<table style="border-collapse: collapse; width: 190px; height: 440px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 84pt;" width="112"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 84pt;" width="112" height="20">2/3/2009 15:29:53</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">2/3/2009 15:34:21</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">2/3/2009 22:15:49</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">2/3/2009 22:17:26</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">2/4/2009 12:30:47</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">2/4/2009 12:30:49</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">2/4/2009 14:07:03</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">2/4/2009 14:19:21</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">2/5/2009 15:27:00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">2/5/2009 16:29:37</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">2/5/2009 22:06:44</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">2/5/2009 22:06:45</td> </tr> </tbody></table>

What I need to produce the information in a separate column/row to look like this....

<table style="border-collapse: collapse; width: 168pt;" border="0" cellpadding="0" cellspacing="0" width="224"><col style="width: 84pt;" span="2" width="112"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 84pt;" width="112" height="20">First</td> <td style="width: 84pt;" width="112">Last</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">2/3/2009 15:29:53</td> <td class="xl65">2/3/2009 22:17:26</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">2/4/2009 12:30:47</td> <td class="xl65">2/4/2009 14:19:21</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">2/5/2009 15:27:00</td> <td class="xl65">2/5/2009 22:06:45</td> </tr> </tbody></table>

Any ideas? rather than manually scrolling thru, copy and pasting them.... which takes forever when your dealing with a 30 day report with around 100 entries per day (have have to reproduce the information for around 20 samples)

 

[texasalynn]

try using rank

Excel Workbook
	F	G
43	2/3/2009 15:29	12
44	2/3/2009 15:34	11
45	2/3/2009 22:15	10
46	2/3/2009 22:17	9
47	2/4/2009 12:30	8
48	2/4/2009 12:30	7
49	2/4/2009 14:07	6
50	2/4/2009 14:19	5
51	2/5/2009 15:27	4
52	2/5/2009 16:29	3
53	2/5/2009 22:06	2
54	2/5/2009 22:06	1
Sheet1

 

[normb4]

that give them a number value, but it doesn't make it any easier to identify what is the first time and last time for each day in the range (or put them in to other rows)

 

[texasalynn]

why not use the rank then you would be able to sort off that field to put the items in the order you wanted

 

[xld]

=INDEX(B:B,MIN(IF($A$1:$A$12=--"2009-03-02",IF($B$1:$B$12=MIN($B$1:$B$12),ROW($B$1:$B$12)))))

and

=INDEX(B:B,MIN(IF($A$1:$A$12=--"2009-03-02",IF($B$1:$B$12=MIN($B$1:$B$12),ROW($B$1:$B$12)))))

both array formulae

 

[normb4]

texasalynn; I wish it was that simple, but they are already sorted into order by earliest to latest, so giving a number value doesn't help idenify the sort order any better.

The dilemma is identifying the first time of the day and the last time of the day for each day in range.

so i have a long column showing dates and times for a 30 day period and numerous entries for each day. What I need to quickly find is the first entry and the last entry for each day of that 30 day range.

 

[normb4]

xld: I'm playing with this option right now - i'll let you know how it goes

 

[Numberz]

Not the complete solution your are looking for, but you could add this formula to show the the earliest and last entry for the date

=IF(INT(A2)=INT(A1),"",A1)

Then Filter the column for non-blanks to only see 1st and last entries for the day.

 

[mikerickson]

The Array (INT(A1:A100)=DATEVALUE("2/3/2009"),MOD(A1:A100,1)) will return an array of times. The CSE formula

=MIN(INT(A1:A100)=DATEVALUE("2/3/2009"),MOD(A1:A100,1)) should return the first time on Feb 3, 2009.

=MAX(..) will return the last.


These formulas need to be confirmed with Ctrl-Shift-Enter (Cmd+Return for Mac)

 

[normb4]

mikerickson.... This is working.... or darn close to working. Is there away I can have the datevalue point to another cell rather than a exact text.

This way I can designated the dates I need the first and last value of.

I tired the tradition way (=B2) but datevalue seems to want "text" only