# Thread: Testing Date Ranges in Excel (97)

1. ## Testing Date Ranges in Excel (97)

Hello,

I am trying to set up a formula with an IF and SUM combination in order to test a cell value with a date and then to add the amount due to a running sum. For example, I have due dates in cells C3:G3 and amounts due in cells C2:G2. I would like to be able to test the date values to be between 1/7/01 and 30/6/02 (Australian Financial Year). If TRUE, then I would like to be able to SUM the values in the related row above (C2:G2). I have tried a number of combinations but with no joy. For example:

=SUM(IF(OR(C3:G3>=1/7/1,C3:G3<=30/6/2),C2:G2))
=SUM(IF(AND(C3:G3>=1/7/1,C3:G3<=30/6/2),C2:G2))
etc.

I look forward to hearing from someone.

Thanks in anticipation.

Jocelyn Browning

2. ## Re: Testing Date Ranges in Excel (97)

One problem is that Excel interprets 30/6/2 in a formula as a division, not as a date.

It's easiest if you put the start and end dates in cells, for example: cell A1 contains 1/7/01 and cell B1 contains 30/6/02.
Enter the following formula in a cell:

=SUM((C3:G3>=A1)*(C3:G3<=B1)*C2:G2)

and confirm it with Ctrl+Shift+Enter instead of just Enter. This makes the formula into an array formula.

If you'd rather have the dates in the formula itself, you can use the DATE or DATEVALUE function. For instance, replace A1 by DATE(2001,7,1) or by DATEVALUE("1/7/01"). Be aware that the DATEVALUE function depends on the regional settings of the computer (it would interpret 1/7/1 as the first of July under US settings).

3. ## Re: Testing Date Ranges in Excel (97)

Thanks HansV,

That was an amazingly quick response! I am impressed.

I shall give it a try.

Regards, Jocelyn
Jocelyn

4. ## Re: Testing Date Ranges in Excel (97)

Although I like Hans' solution because it makes it easy to change the date range being summed, if you don't want to put the dates in separate cells, you could also use:

<pre>=SUM((C3:G3>=DATEVALUE("1/7/01"))*(C3:G3<=DATEVALUE("30/6/02"))*C2:G2)
</pre>

5. ## Re: Testing Date Ranges in Excel (97)

I prefer (for AND)

=SUM(if((C3:G3>=A1)*(C3:G3<=B1),C2:G2))

And (for OR)
=SUM(if((C3:G3>=A1)+(C3:G3<=B1),C2:G2))

It is longer, yes, but it is easier to modify and change the sum to average, min, max, stdev, etc and make the array formula that much more general.

Steve

6. ## Re: Testing Date Ranges in Excel (97)

Thanks HansV,

This solution worked wonders - much appreciated.

Regards,
Jocelyn

7. ## Re: Testing Date Ranges in Excel (97)

Thanks Legare,

Much appreciated.
Cheers,
Jocelyn

8. ## Re: Testing Date Ranges in Excel (97)

Thanks Steve,

It never ceases to amaze me how helpful people can be if you just ask.

Cheers,
Jocelyn

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•