Date Sorting

[Guest]

I pull data from another application. In Column A it returns a list list of
times stampes by agent actvites in a "2/20/2006 4:38:37 PM" format. For
every event of the day that something changes the data will stamp the event
in another cell in that same column. What I want to be able to do is to
identify the first activity of each day in this list. There can be easily
several days (making hundreads/thousands of rows) worth of information and it
easy sort them column in order but I want to quickly find the first event of
each day. Preferably pulling the first event of each new day onto antoher
spreadsheet or area in the workbook.

I hope this makes since?
The list looks something like this
Column A
1 2/20/06 7:59 AM
2 2/20/06 8:00 AM
3 2/20/06 8:34 AM
4 2/20/06 8:34 AM
5 2/20/06 8:34 AM
6 2/20/06 9:05 AM
7 2/21/06 8:02 AM
8 2/21/06 8:47 AM
9 2/21/06 9:29 AM
10 2/21/06 9:29 AM
11 2/21/06 9:29 AM
12 2/21/06 9:33 AM
13 2/21/06 9:34 AM
14 2/21/06 9:42 AM
15 2/22/06 8:02 AM
16 2/22/06 8:02 AM
17 2/22/06 8:02 AM
18 2/22/06 8:18 AM
19 2/22/06 8:19 AM
20 2/22/06 8:32 AM
21 2/22/06 8:34 AM

What I want to see is below
2/20/06 7:59 AM
2/21/06 8:02 AM
2/22/06 8:02 AM

 

[Pete_UK]

I put your date/times into column A, occupying A1 to A21. This formula
went into cell B1:

=INT(MIN(A1:A21))

and this finds the earliest date in the list. Then put this formula in
F1:

=MIN(IF(INT(A$1:A$21)=B$1+ROW()-1,A$1:A$21,100000))

This is an array formula, which means that you do not press ENTER in
the normal way once you have typed the formula in - instead, you have
to do CTRL-SHIFT-ENTER at the same time. If you do it correctly, Excel
will wrap curly braces { } around the formula (do not type these
yourself).

Then you can just copy the formula down column F for as many days as
you think there are in the data set. If you don't want to guess this,
then this formula in C1:

=INT(MAX(A1:A21))

and this in D1:

= C1 - B1 + 1 ,(formatted as number, 0dp)

will tell you how many days you have.

Hope this helps.

Pete

 

[Guest]

Use Autofilter:

if your datetime data is in column A them put a label cell on the top of
cols A&B

In B2 put 1
In B3 put =IF(INT(A3)=INT(A2),0,1) and copy down

Notice col B shows a 1 at the first record of a given day. Pull-down:

Filter > Autofilter and click the column B selector to show only 1's