Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In a spreadsheet with one date per row, how do I query a date range? Say I want EMPLOYEES with ABSENCES between June 1, 2009 and September 1, 2009. How do I structure the uery?

    Any help will be appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    How would you like to query the data - do you want to filter them using AutoFilter or Advanced Filter, or do you want to perform a calculation, for example count or sum entries within a specified date range?

  3. #3
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='797348' date='11-Oct-2009 10:28']How would you like to query the data - do you want to filter them using AutoFilter or Advanced Filter, or do you want to perform a calculation, for example count or sum entries within a specified date range?[/quote]
    I'm doing everything else on this project with autofilter, so let's try autofilter.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    It depends on how you structured your table. If you have an "absence date" column:

    - Click the dropdown arrow in the absence date column header.
    - Select Custom... from the dropdown list.
    - Select "Greater than or equal to" from the first dropdown.
    - Select June 1, 2009 (or the nearest available date) from the list of values in the dropdown to the right of it.
    - Make sure that the "And" radio button is selected (this is the default).
    - Select "Less than" from the dropdown below that.
    - Select September 1, 2009 (or the nearest available date) from the list of values in the dropdown to the right of it.
    - Click OK.

  5. #5
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='797420' date='12-Oct-2009 01:32']It depends on how you structured your table. If you have an "absence date" column:

    - Click the dropdown arrow in the absence date column header.
    - Select Custom... from the dropdown list.
    - Select "Greater than or equal to" from the first dropdown.
    - Select June 1, 2009 (or the nearest available date) from the list of values in the dropdown to the right of it.
    - Make sure that the "And" radio button is selected (this is the default).
    - Select "Less than" from the dropdown below that.
    - Select September 1, 2009 (or the nearest available date) from the list of values in the dropdown to the right of it.
    - Click OK.[/quote]
    That works, but when I try to automate it using a listbox for the FROM date and a second lixtbox for the TO date I'm having problems. The variables lstFromdate and lstTodate take their values, but are not transferring them to autofilter.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The attached sample workbook shows how it can be done. The code behind the OK button on the userform is:

    Code:
    Private Sub cmdOK_Click()
      If CDate(Me.lstTo) < CDate(Me.lstFrom) Then
    	MsgBox "The 'To' date should not be before the 'From' date!", _
    	  vbExclamation
    	Exit Sub
      End If
      Worksheets("Sheet1").Range("A4").AutoFilter _
    	Field:=1, Criteria1:=">=" & Me.lstFrom, _
    	Criteria2:="<=" & Me.lstTo
      Unload Me
    End Sub
    Attached Files Attached Files

  7. #7
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='797864' date='14-Oct-2009 07:35']The attached sample workbook shows how it can be done. The code behind the OK button on the userform is:

    Code:
    Private Sub cmdOK_Click()
      If CDate(Me.lstTo) < CDate(Me.lstFrom) Then
    	MsgBox "The 'To' date should not be before the 'From' date!", _
    	  vbExclamation
    	Exit Sub
      End If
      Worksheets("Sheet1").Range("A4").AutoFilter _
    	Field:=1, Criteria1:=">=" & Me.lstFrom, _
    	Criteria2:="<=" & Me.lstTo
      Unload Me
    End Sub
    [/quote]
    Tis worked perfectly! Thanks.

Posting Permissions

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