Remember Me?

#1
August 21st 06, 01:37 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 1
Identify a range of cells containing dates

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

--
RJSohn
------------------------------------------------------------------------
RJSohn's Profile: http://www.excelforum.com/member.php...o&userid=37799

#2
August 21st 06, 02:09 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 9,221
Identify a range of cells containing dates

"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()),MONT H(TODAY())-3,DAY(TODAY())))*(\$A\$1:\$A\$1000<=TODAY()),\$B\$1:\$B\$1 000)

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
---
#3
August 24th 06, 02:43 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 9,221
Identify a range of cells containing dates

Typo in earlier response just detected ..

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

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

Formula's OK, though <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Dennis Excel Discussion (Misc queries) 10 March 2nd 06 10:47 PM Dave Excel Discussion (Misc queries) 1 February 1st 06 11:55 PM Les Excel Worksheet Functions 10 July 28th 05 11:54 AM Alex Excel Worksheet Functions 2 July 27th 05 04:15 PM Tremain Excel Worksheet Functions 1 May 9th 05 10:55 PM

All times are GMT +1. The time now is 02:48 PM.