select only last 30 days of a work sheet

[Tyler]

I need to filter out only the last x number of days/hours of data from my
work sheet. The worksheet posts new data every 5 minutes. I need to extract
the newest data points. The time will vary from 7 days to 90 days.

My existing data has a date/time combination. It looks like
this,"12/19/2008 19:10"

 

[Max]

Something along these lines might deliver it here ..

Let's say you want to dynamically extract the last (ie "lowest") 7 lines of
source data into another sheet, based on a key col, where the key col would
contain contiguous source data populated down (ie with no empty cells
in-between data)

Source data assumed in Sheet1, cols A to G, col headers in row1, data in
row2 down, with key col A (date). It's assumed there's at least 7 lines of
source data to start with.

In Sheet2,
with the same col headers pasted in A1:G1

Place this in A2
=OFFSET(INDIRECT("'Sheet1'!A"&COUNTA(Sheet1!$A:$A)-6),MOD(ROWS($1:1)-1,7),COLUMNS($A:A)-1)

Copy A2 across to G2, fill down to G8. Format the cols accordingly. A2:G8
will dynamically return the last 7 lines of source data in Sheet1 as source
data is continuously populated there.

Adapt to suit the number of lines desired:
in ..COUNTA(Sheet1!$A:$A)-6 : the "6" is 7 lines -1
in ..MOD(ROWS($1:1)-1,7) : the 7 = 7 lines
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik

 

[smartin]

I need to filter out only the last x number of days/hours of data from my
work sheet. The worksheet posts new data every 5 minutes. I need to extract
the newest data points. The time will vary from 7 days to 90 days.

My existing data has a date/time combination. It looks like
this,"12/19/2008 19:10"

Here's a possibility.

I set up a time series in A:A, and random values in B:B.

First determine the date of the last entry:
D2=INT(MAX(A:A))

If you don't want to roll back to midnight, eliminate the INT() portion.

Next figure out the value of the first entry that is, say, 30 days back:
E2=D2-30

Next determine where the 30-day back record is located:
F2=MATCH(E2,A:A,1)

And determine where the last record is located:
G2=COUNTA(A:A)

Lastly, create a range based on what we know:
H2=SUM(OFFSET($A$1,F2,1,G2-F2,1))

This will sum the values in the last 30-day range. If you are charting,
you can just use the OFFSET piece as a data value source:

=OFFSET($A$1,F2,1,G2-F2,1)

The corresponding x- or category value source would be:

=OFFSET($A$1,F2,0,G2-F2,1)

Of course you could do all this without the extra helper columns, but I
thought it might help to see the steps.

Hope this helps!