Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>

    Legare Coleman

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #6
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Testing Date Ranges in Excel (97)

    Thanks HansV,

    This solution worked wonders - much appreciated.

    Regards,
    Jocelyn

  7. #7
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Testing Date Ranges in Excel (97)

    Thanks Legare,

    Much appreciated.
    Cheers,
    Jocelyn

  8. #8
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •