Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 View this thread: http://www.excelforum.com/showthread...hreadid=573606 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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: should have read as: 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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
Macro to hide blank cells in a range | Excel Discussion (Misc queries) | |||
Match function...random search? | Excel Worksheet Functions | |||
count date occurances in range of dates... | Excel Worksheet Functions | |||
Identifying Overlapping Dates within a range | Excel Worksheet Functions |