Identify a range of cells containing dates

[RJSohn]

I need a formula that will identify a range of cells that contain dates, related to rows of data. (example: A1..A52 contain dates, find the last 3 months worth of entries, calculated from a cell that contains <today> function

[Max]

"RJSohn" wrote:
> I need a formula that will identify a range of cells that contain dates,
> related to rows of data. (example: A1..A52 contain dates, find the last
> 3 months worth of entries, calculated from a cell that contains <today>
> function

One interp and an example to illustrate ..

Suppose we have real dates expected within say: A1:A1000, with corresponding
numeric values (eg sales figs) input within B1:B1000

Then if we want to calc a running total sales for the last 30 days
(inclusive today), we could put in say, C1:

=SUMPRODUCT(($A$1:$A$1000>=DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY())))*($A$1:$A$1000<=TODAY()),$B$1:$B$1000)

The core expression which determines the qualifying "range" of dates within
A1:A1000 is given by the part:

($A$1:$A$1000>=DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY())))*($A$1:$A$1000<=TODAY())

which evaluates to an array of 1's/0's depending on whether the dates
qualify or not

The "range" may be contiguous or discontiguous depending on whether the
inputs within A1:A1000 are sequentially made or not. This is immaterial in
the example application above.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[RJSohn]

Thanks, I'll give this a try. Looks like it may be what I'm looking for.

[RJSohn]

To Max:
I need to take this formula to the next step, it works for dates and the related column of product in a single column range but is it possible to use it when the related columns are continued to a second set of ranges? Or on another sheet?
I have a sample worksheet as an example that I can e-mail direct if that works better. just let me know if you can help me further.
Thanks,
Ron (rjsohn@toast2.net)

[RJSohn]

To Max:
I need to take this formula to the next step, it works for dates and the related column of product in a single column range but is it possible to use it when the related columns are continued to a second set of ranges? Or on another sheet?
I have a sample worksheet as an example that I can e-mail direct if that works better. just let me know if you can help me further.
Thanks,
Ron (rjsohn@toast2.net)

"RJSohn" wrote:
> I need a formula that will identify a range of cells that contain dates,
> related to rows of data. (example: A1..A52 contain dates, find the last
> 3 months worth of entries, calculated from a cell that contains <today>
> function

One interp and an example to illustrate ..

Suppose we have real dates expected within say: A1:A1000, with corresponding
numeric values (eg sales figs) input within B1:B1000

Then if we want to calc a running total sales for the last 30 days
(inclusive today), we could put in say, C1:

=SUMPRODUCT(($A$1:$A$1000>=DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY())))*($A$1:$A$1000<=TODAY()),$B$1:$B$1000)

The core expression which determines the qualifying "range" of dates within
A1:A1000 is given by the part:

($A$1:$A$1000>=DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY())))*($A$1:$A$1000<=TODAY())

which evaluates to an array of 1's/0's depending on whether the dates
qualify or not

The "range" may be contiguous or discontiguous depending on whether the
inputs within A1:A1000 are sequentially made or not. This is immaterial in
the example application above.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---