Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old 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
Default 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   Report Post  
Old August 21st 06, 02:09 AM posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default 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   Report Post  
Old August 24th 06, 02:43 AM posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy/Paste how to avoid the copy of formula cells w/o calc values Dennis Excel Discussion (Misc queries) 10 March 2nd 06 11:47 PM
Macro to hide blank cells in a range Dave Excel Discussion (Misc queries) 1 February 2nd 06 12:55 AM
Match function...random search? Les Excel Worksheet Functions 10 July 28th 05 11:54 AM
count date occurances in range of dates... Alex Excel Worksheet Functions 2 July 27th 05 04:15 PM
Identifying Overlapping Dates within a range Tremain Excel Worksheet Functions 1 May 9th 05 10:55 PM


All times are GMT +1. The time now is 09:11 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017