Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Filtering Dates in a table with input boxes
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 87
Default Filtering Dates in a table with input boxes
Hi,

I am trying to filter dates in a table based on two input box
values...the start date and the end date...


The only way I can see to do this is to enter the last day of the
months that will be in the report...is there any way to make it so
that the user can input specific dates to pull a report for those
specific dates?

This is what I came up with so far...


MYFILTER1 = InputBox _
("Please enter the first day of the period you would like to
generate this report for:", _
"First Date")
MYFILTER2 = InputBox _
("Please enter the last day of the period you would like to
generate this report for:", _
"End Date", range("J1"))



ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1,
Operator:= _
xlFilterValues, Criteria2:=Array(1, MYFILTER1, 1, MYFILTER2)
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 342
Default Filtering Dates in a table with input boxes
Try this:

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1, _
Criteria1:="=MYFILTER1,3", Operator:=xlAnd, _
Criteria2:="<=MYFILTER2"

Tom


"R Tanner" wrote:

Hi,

I am trying to filter dates in a table based on two input box
values...the start date and the end date...


The only way I can see to do this is to enter the last day of the
months that will be in the report...is there any way to make it so
that the user can input specific dates to pull a report for those
specific dates?

This is what I came up with so far...


MYFILTER1 = InputBox _
("Please enter the first day of the period you would like to
generate this report for:", _
"First Date")
MYFILTER2 = InputBox _
("Please enter the last day of the period you would like to
generate this report for:", _
"End Date", range("J1"))



ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1,
Operator:= _
xlFilterValues, Criteria2:=Array(1, MYFILTER1, 1, MYFILTER2)

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Filtering Dates in a table with input boxes
If you need the date of the last day in the month of the date the user
inputs, give this a try...

MYFILTER1 = InputBox("Please enter the first day of the period " & _
"you would like to generate this report for:", "First Date")
If IsDate(MYFILTER1) Then
MYFILTER1 = DateSerial(Year(MYFILTER1), Month(MYFILTER1) + 1, 0)
End If

Rick


"R Tanner" wrote in message
...
Hi,

I am trying to filter dates in a table based on two input box
values...the start date and the end date...


The only way I can see to do this is to enter the last day of the
months that will be in the report...is there any way to make it so
that the user can input specific dates to pull a report for those
specific dates?

This is what I came up with so far...


MYFILTER1 = InputBox _
("Please enter the first day of the period you would like to
generate this report for:", _
"First Date")
MYFILTER2 = InputBox _
("Please enter the last day of the period you would like to
generate this report for:", _
"End Date", range("J1"))



ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1,
Operator:= _
xlFilterValues, Criteria2:=Array(1, MYFILTER1, 1, MYFILTER2)

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 87
Default Filtering Dates in a table with input boxes
On Aug 15, 9:36 am, "Rick Rothstein \(MVP - VB\)"
wrote:
If you need the date of the last day in the month of the date the user
inputs, give this a try...

MYFILTER1 = InputBox("Please enter the first day of the period " & _
"you would like to generate this report for:", "First Date")
If IsDate(MYFILTER1) Then
MYFILTER1 = DateSerial(Year(MYFILTER1), Month(MYFILTER1) + 1, 0)
End If

Rick

"R Tanner" wrote in message

...

Hi,

I am trying to filter dates in a table based on two input box
values...the start date and the end date...

The only way I can see to do this is to enter the last day of the
months that will be in the report...is there any way to make it so
that the user can input specific dates to pull a report for those
specific dates?

This is what I came up with so far...

MYFILTER1 = InputBox _
("Please enter the first day of the period you would like to
generate this report for:", _
"First Date")
MYFILTER2 = InputBox _
("Please enter the last day of the period you would like to
generate this report for:", _
"End Date", range("J1"))

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1,
Operator:= _
xlFilterValues, Criteria2:=Array(1, MYFILTER1, 1, MYFILTER2)

What I am really trying to do is get the first and last date of the
period for the report from the user. Not necessarily just the last
date...I'm not sure if I made that clear or not...

Thanks for your input...
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 87
Default Filtering Dates in a table with input boxes
I tried your idea with the filter 1 and 2 and the criteria but it did
not work...it didn't unselected everything in the table rather than
just the specified dates...
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 342
Default Filtering Dates in a table with input boxes
I erroneously included ,3. This should work better.

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1, _
Criteria1:="=MYFILTER1", Operator:=xlAnd, _
Criteria2:="<=MYFILTER2"


"TomPl" wrote:

Try this:

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1, _
Criteria1:="=MYFILTER1,3", Operator:=xlAnd, _
Criteria2:="<=MYFILTER2"

Tom


"R Tanner" wrote:

Hi,

I am trying to filter dates in a table based on two input box
values...the start date and the end date...


The only way I can see to do this is to enter the last day of the
months that will be in the report...is there any way to make it so
that the user can input specific dates to pull a report for those
specific dates?

This is what I came up with so far...


MYFILTER1 = InputBox _
("Please enter the first day of the period you would like to
generate this report for:", _
"First Date")
MYFILTER2 = InputBox _
("Please enter the last day of the period you would like to
generate this report for:", _
"End Date", range("J1"))



ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1,
Operator:= _
xlFilterValues, Criteria2:=Array(1, MYFILTER1, 1, MYFILTER2)

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 342
Default Filtering Dates in a table with input boxes
The code should filter to show everything between the two dates inclusive.
Can't imagine why it doesn't work.

"R Tanner" wrote:

I tried your idea with the filter 1 and 2 and the criteria but it did
not work...it didn't unselected everything in the table rather than
just the specified dates...

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 87
Default Filtering Dates in a table with input boxes
On Aug 15, 10:09 am, TomPl wrote:
I erroneously included ,3. This should work better.

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1, _
Criteria1:="=MYFILTER1", Operator:=xlAnd, _
Criteria2:="<=MYFILTER2"

"TomPl" wrote:
Try this:

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1, _
Criteria1:="=MYFILTER1,3", Operator:=xlAnd, _
Criteria2:="<=MYFILTER2"

Tom

"R Tanner" wrote:

Hi,

I am trying to filter dates in a table based on two input box
values...the start date and the end date...

The only way I can see to do this is to enter the last day of the
months that will be in the report...is there any way to make it so
that the user can input specific dates to pull a report for those
specific dates?

This is what I came up with so far...

MYFILTER1 = InputBox _
("Please enter the first day of the period you would like to
generate this report for:", _
"First Date")
MYFILTER2 = InputBox _
("Please enter the last day of the period you would like to
generate this report for:", _
"End Date", range("J1"))

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1,
Operator:= _
xlFilterValues, Criteria2:=Array(1, MYFILTER1, 1, MYFILTER2)

it still didn't work...I have my filters declared as dates...That is
how is should be right?
Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 87
Default Filtering Dates in a table with input boxes
On Aug 15, 10:09 am, TomPl wrote:
I erroneously included ,3. This should work better.

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1, _
Criteria1:="=MYFILTER1", Operator:=xlAnd, _
Criteria2:="<=MYFILTER2"

"TomPl" wrote:
Try this:

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1, _
Criteria1:="=MYFILTER1,3", Operator:=xlAnd, _
Criteria2:="<=MYFILTER2"

Tom

"R Tanner" wrote:

Hi,

I am trying to filter dates in a table based on two input box
values...the start date and the end date...

The only way I can see to do this is to enter the last day of the
months that will be in the report...is there any way to make it so
that the user can input specific dates to pull a report for those
specific dates?

This is what I came up with so far...

MYFILTER1 = InputBox _
("Please enter the first day of the period you would like to
generate this report for:", _
"First Date")
MYFILTER2 = InputBox _
("Please enter the last day of the period you would like to
generate this report for:", _
"End Date", range("J1"))

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1,
Operator:= _
xlFilterValues, Criteria2:=Array(1, MYFILTER1, 1, MYFILTER2)

it still didn't work...I have my filters declared as dates...That is
how is should be right?
Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 87
Default Filtering Dates in a table with input boxes
On Aug 15, 10:09 am, TomPl wrote:
I erroneously included ,3. This should work better.

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1, _
Criteria1:="=MYFILTER1", Operator:=xlAnd, _
Criteria2:="<=MYFILTER2"

"TomPl" wrote:
Try this:

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1, _
Criteria1:="=MYFILTER1,3", Operator:=xlAnd, _
Criteria2:="<=MYFILTER2"

Tom

"R Tanner" wrote:

Hi,

I am trying to filter dates in a table based on two input box
values...the start date and the end date...

The only way I can see to do this is to enter the last day of the
months that will be in the report...is there any way to make it so
that the user can input specific dates to pull a report for those
specific dates?

This is what I came up with so far...

MYFILTER1 = InputBox _
("Please enter the first day of the period you would like to
generate this report for:", _
"First Date")
MYFILTER2 = InputBox _
("Please enter the last day of the period you would like to
generate this report for:", _
"End Date", range("J1"))

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1,
Operator:= _
xlFilterValues, Criteria2:=Array(1, MYFILTER1, 1, MYFILTER2)

it still didn't work...I have my filters declared as dates...That is
how is should be right?
Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 87
Default Filtering Dates in a table with input boxes
On Aug 15, 10:09 am, TomPl wrote:
I erroneously included ,3. This should work better.

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1, _
Criteria1:="=MYFILTER1", Operator:=xlAnd, _
Criteria2:="<=MYFILTER2"

"TomPl" wrote:
Try this:

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1, _
Criteria1:="=MYFILTER1,3", Operator:=xlAnd, _
Criteria2:="<=MYFILTER2"

Tom

"R Tanner" wrote:

Hi,

I am trying to filter dates in a table based on two input box
values...the start date and the end date...

The only way I can see to do this is to enter the last day of the
months that will be in the report...is there any way to make it so
that the user can input specific dates to pull a report for those
specific dates?

This is what I came up with so far...

MYFILTER1 = InputBox _
("Please enter the first day of the period you would like to
generate this report for:", _
"First Date")
MYFILTER2 = InputBox _
("Please enter the last day of the period you would like to
generate this report for:", _
"End Date", range("J1"))

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1,
Operator:= _
xlFilterValues, Criteria2:=Array(1, MYFILTER1, 1, MYFILTER2)

it still didn't work...I have my filters declared as dates...That is
how is should be right?
Reply With QuoteReply With Quote
  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 342
Default Filtering Dates in a table with input boxes
Sorry,

Replace this:

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1, _
Criteria1:="=MYFILTER1", Operator:=xlAnd, _
Criteria2:="<=MYFILTER2"

With this:

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1, _
Criteria1:="=" & MYFILTER1, Operator:=xlAnd, _
Criteria2:="<=" & MYFILTER2

It was hard to test because I don't have your worksheet.
Remember to enter the dates in a format that excel recognises as a date.
MYFILTER1 & MYFILTER2 should be declared as dates.

Hope this works.

"R Tanner" wrote:

On Aug 15, 10:09 am, TomPl wrote:
I erroneously included ,3. This should work better.

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1, _
Criteria1:="=MYFILTER1", Operator:=xlAnd, _
Criteria2:="<=MYFILTER2"

"TomPl" wrote:
Try this:

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1, _
Criteria1:="=MYFILTER1,3", Operator:=xlAnd, _
Criteria2:="<=MYFILTER2"

Tom

"R Tanner" wrote:

Hi,

I am trying to filter dates in a table based on two input box
values...the start date and the end date...

The only way I can see to do this is to enter the last day of the
months that will be in the report...is there any way to make it so
that the user can input specific dates to pull a report for those
specific dates?

This is what I came up with so far...

MYFILTER1 = InputBox _
("Please enter the first day of the period you would like to
generate this report for:", _
"First Date")
MYFILTER2 = InputBox _
("Please enter the last day of the period you would like to
generate this report for:", _
"End Date", range("J1"))

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1,
Operator:= _
xlFilterValues, Criteria2:=Array(1, MYFILTER1, 1, MYFILTER2)

it still didn't work...I have my filters declared as dates...That is
how is should be right?

Reply With QuoteReply With Quote
  #13   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 87
Default Filtering Dates in a table with input boxes
On Aug 15, 2:19 pm, TomPl wrote:
Sorry,

Replace this:

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1, _
Criteria1:="=MYFILTER1", Operator:=xlAnd, _
Criteria2:="<=MYFILTER2"

With this:

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1, _
Criteria1:="=" & MYFILTER1, Operator:=xlAnd, _
Criteria2:="<=" & MYFILTER2

It was hard to test because I don't have your worksheet.
Remember to enter the dates in a format that excel recognises as a date.
MYFILTER1 & MYFILTER2 should be declared as dates.

Hope this works.

"R Tanner" wrote:
On Aug 15, 10:09 am, TomPl wrote:
I erroneously included ,3. This should work better.

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1, _
Criteria1:="=MYFILTER1", Operator:=xlAnd, _
Criteria2:="<=MYFILTER2"

"TomPl" wrote:
Try this:

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1, _
Criteria1:="=MYFILTER1,3", Operator:=xlAnd, _
Criteria2:="<=MYFILTER2"

Tom

"R Tanner" wrote:

Hi,

I am trying to filter dates in a table based on two input box
values...the start date and the end date...

The only way I can see to do this is to enter the last day of the
months that will be in the report...is there any way to make it so
that the user can input specific dates to pull a report for those
specific dates?

This is what I came up with so far...

MYFILTER1 = InputBox _
("Please enter the first day of the period you would like to
generate this report for:", _
"First Date")
MYFILTER2 = InputBox _
("Please enter the last day of the period you would like to
generate this report for:", _
"End Date", range("J1"))

ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1,
Operator:= _
xlFilterValues, Criteria2:=Array(1, MYFILTER1, 1, MYFILTER2)

it still didn't work...I have my filters declared as dates...That is
how is should be right?

Thank you Tom...you are awesome...this did work...
Reply With QuoteReply With Quote
  #14   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,123
Default Filtering Dates in a table with input boxes
Try EasyFilter
http://www.rondebruin.nl/easyfilter.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"R Tanner" wrote in message ...
Hi,

I am trying to filter dates in a table based on two input box
values...the start date and the end date...


The only way I can see to do this is to enter the last day of the
months that will be in the report...is there any way to make it so
that the user can input specific dates to pull a report for those
specific dates?

This is what I came up with so far...


MYFILTER1 = InputBox _
("Please enter the first day of the period you would like to
generate this report for:", _
"First Date")
MYFILTER2 = InputBox _
("Please enter the last day of the period you would like to
generate this report for:", _
"End Date", range("J1"))



ActiveSheet.ListObjects("WDT").range.AutoFilter Field:=1,
Operator:= _
xlFilterValues, Criteria2:=Array(1, MYFILTER1, 1, MYFILTER2)
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
filtering dates in a Pivot Table Report Filter jsmith Excel Discussion (Misc queries) 3 November 11th 09 03:27 AM
Pivot Table filtering dates KJ Excel Discussion (Misc queries) 1 April 24th 07 10:21 PM
Filtering by Check Boxes Eggtavius Excel Discussion (Misc queries) 2 January 18th 06 04:41 AM
How do I add input data in the input ranges in drop down boxes. oil_driller Excel Discussion (Misc queries) 1 November 9th 05 10:31 PM
Mask input in input boxes? Eric[_14_] Excel Programming 4 November 29th 03 11:10 AM


All times are GMT +1. The time now is 08:51 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement