1. Registered User
Join Date
Aug 2003
Posts
328

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. Registered User
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)

3. Registered User
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 14:46.

4. Registered User
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.

5. Registered User
Join Date
Oct 2003
Posts
1,091
The filter will work. Try using Advanced Filter for up to three criteria at once.

6. Registered User
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. Registered User
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. Registered User
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.

9. Registered User
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
•