Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2003
    Posts
    328

    Unanswered: Date Range Question

    I was wondering how I can let a user select a date range from a table.
    For example:

    Date_column Sales
    1/1/04 $10000
    12/22/03 $25000
    8/9/03 $27500
    9/10/03 $43000
    7/2/03 $18000
    10/19/03 $53000

    I want the user to be able to enter a starting date and an ending date to see only those sales between the entered dates.
    Start Date:8/9/03
    End Date: 12/22/03

    Result:
    8/9/03 $27500
    9/10/03 $43000
    10/19/03 $53000
    12/22/03 $25000

    Can I do this with a formula?

    Thanks.

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    If you want totals sales between those dates, yes you can do it with a formula.

    Assume Dates in G5:G10, and Sales in H5:H10, then put in J4 the word "Start" (without "), and in K4 put "end"( w/o "). Then in J5 put 8/9/2003 and in K5 put 12/22/2003. Then put this formula in L5

    =SUMPRODUCT(($G$5:$G$10>=J5)*($G5:$G$10<=K5),$H$5: $H$10)
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Aug 2003
    Posts
    328
    I don't need the sales added in. I just want to be able to enter a date range and return the whole line for those dates. It looks like I can only do this with some kind of a filter. Is this true? I found it. I need to do auto filter and then custom filter.
    Thanks.
    Last edited by exdter; 01-30-04 at 15:46.

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Okay, assume the same setup as I outlined above. Then in L5 put this formula:

    =IF(AND(G5>=J$5)*(G5<=K$5),G5,"")

    then copy this down as far as the list in column G (some should lacks)

    In M5 put this formula:

    =IF(L5="","",H5)

    Then copy down as far as the list in column G.

    This will provide only those that are between the specified dates, but it will leave some lines blank. If you want to hide rows, then that will depend on VBA.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    The filter will work. Try using Advanced Filter for up to three criteria at once.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    Join Date
    Aug 2003
    Posts
    328
    I tried with the Advanced Filter and get an error. Maybe I'm doing it wrong. What should the criteria range window look like?

  7. #7
    Join Date
    Aug 2003
    Posts
    328
    I click on the dates I want the criteria to be between and all the lines in my table disappear. I put is greater than or equal to 'low_date' and is less than or equal to 'high_date'
    Thanks alot for your help and patience.

  8. #8
    Join Date
    Oct 2003
    Posts
    1,091
    Sorry for the confusion. I am working on a project as was trying to go by memory and keep up with the project.

    It is Advanced Filter that you want. For one time, criteria use the regular filter. For changing criteria use advanced filter.

    I have to run. But will try to get back.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  9. #9
    Join Date
    Aug 2003
    Posts
    328
    Thanks for now.

Posting Permissions

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