Date difference in range

[jrevard]

Hi,

I have several rows that contain two dates (in two columns). I would like to test each row to see if the dates differ by more than 7 days.

For example, A1:A10 could contain the "Start Date" and B1:B10 could contain the "End Date." Is there a formula I could put in C1 that would tell me how many of the End Dates are more than 7 days later than the Start Date? I would need this to work for varying row quantities (i.e. row quantity is unknown).

You guys are amazing and I look forward to any advice.

BTW I am trying to avoid VBA, but am comfortable with it if I must use it.

Thanks!

Justin Revard

 

[njimack]

This uses 2 dynamic ranges:
Start_Date: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
End_Date: =OFFSET(Start_Date,0,1)

Sheet1

*	A	B	C
1	22/05/2015	01/06/2015	6
2	23/05/2015	28/05/2015	*
3	24/05/2015	06/06/2015	*
4	25/05/2015	26/05/2015	*
5	26/05/2015	03/06/2015	*
6	27/05/2015	08/06/2015	*
7	28/05/2015	10/06/2015	*
8	29/05/2015	06/06/2015	*
9	30/05/2015	04/06/2015	*
10	31/05/2015	05/06/2015	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:75px;"><col style="width:75px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula C1 =SUMPRODUCT(--(End_Date>Start_Date+7)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[jrevard]

Neil,

Thank you much for the quick and clean response. I am unfamiliar with the dynamic ranges you listed at the beginning of your post (and I notice they are not in the "Spreadsheet Formulas"). Where should I set up these dynamic ranges? In a module or the worksheet code perhaps? I am familiar with VBA, but only with forms and mostly simple code.

Thanks!

Justin Revard

 

[njimack]

Take a look at http://www.contextures.com/xlNames01.html#Dynamic