First/Last Date in a Range

normb4

New Member
Joined
Feb 25, 2009
Messages
10
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)
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
try using rank

Excel Workbook
FG
432/3/2009 15:2912
442/3/2009 15:3411
452/3/2009 22:1510
462/3/2009 22:179
472/4/2009 12:308
482/4/2009 12:307
492/4/2009 14:076
502/4/2009 14:195
512/5/2009 15:274
522/5/2009 16:293
532/5/2009 22:062
542/5/2009 22:061
Sheet1
 

normb4

New Member
Joined
Feb 25, 2009
Messages
10
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

Well-known Member
Joined
May 19, 2002
Messages
8,458
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

Banned
Joined
Feb 8, 2003
Messages
5,378

ADVERTISEMENT

=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

New Member
Joined
Feb 25, 2009
Messages
10
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

New Member
Joined
Feb 25, 2009
Messages
10

ADVERTISEMENT

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

Numberz

New Member
Joined
Feb 13, 2009
Messages
15
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,910
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

New Member
Joined
Feb 25, 2009
Messages
10
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,708
Messages
5,597,688
Members
414,164
Latest member
ARTW

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top