Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identifying the first date(s) from a list
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identifying the first date(s) from a list
How about conditional formatting.
Select all the cells in column A Menu FormatConditional Formatting Change Condition 1 To Formula Is Add a formula of =A1=MIN(IF(INT($A$1:$A$100)=INT(A1),$A$1:$A$100)) Click Format Select the pattern tab Choose a colour Exit -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "amy" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identifying the first date(s) from a list
This is a little closer, but it identifies by identifying the lowest cell.
But what happens in my data is that I have several days in the same listing that I need to find the first entry for each day in that list "Bob Phillips" wrote: How about conditional formatting. Select all the cells in column A Menu FormatConditional Formatting Change Condition 1 To Formula Is Add a formula of =A1=MIN(IF(INT($A$1:$A$100)=INT(A1),$A$1:$A$100)) Click Format Select the pattern tab Choose a colour Exit -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "amy" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identifying the first date(s) from a list
Try this: Assumes data is in column A starting row 2 and is sorted in
ascending date/time sequence Sub FindFirstDate() Dim r As Long, rr As Long, lastrow As Long Dim n As Integer Dim rnga As Range Set ws1 = Worksheets("sheet1") Set ws2 = Worksheets("sheet2") With ws1 lastrow = .Cells(Rows.Count, "A").End(xlUp).Row r = 2 rr = 2 nextrow = r FirstDate = Format(.Cells(r, "A"), "DD/MM/YYYY") For r = 3 To lastrow If Format(.Cells(r, "A"), "DD/MM/YYYY") < Format(.Cells(r - 1, "A"), "DD/MM/YYYY") Then ws2.Cells(rr, "A") = .Cells(nextrow, "A") rr = rr + 1 FirstDate = Format(.Cells(r, "A"), "DD/MM/YYYY") nextrow = r End If Next r ws2.Cells(rr, "A") = .Cells(nextrow, "A") End With End Sub "amy" wrote: This is a little closer, but it identifies by identifying the lowest cell. But what happens in my data is that I have several days in the same listing that I need to find the first entry for each day in that list "Bob Phillips" wrote: How about conditional formatting. Select all the cells in column A Menu FormatConditional Formatting Change Condition 1 To Formula Is Add a formula of =A1=MIN(IF(INT($A$1:$A$100)=INT(A1),$A$1:$A$100)) Click Format Select the pattern tab Choose a colour Exit -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "amy" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Identifying changing data in a list | Excel Discussion (Misc queries) | |||
Identifying more than one item in a list | Excel Discussion (Misc queries) | |||
Identifying bottom of list | Excel Worksheet Functions | |||
Identifying Date Overlaps | Excel Discussion (Misc queries) | |||
Identifying Date Cells | Excel Programming |