如何在两个日期之间查找数据 [英] How to find data between two dates

问题描述

我不经常使用Excel，因此不熟悉复杂的公式.
我有一个这样的Excel 2007工作表:

I do not use Excel regularly, hence I am unfamiliar with complicated formulas.
I have an Excel 2007 sheet like this:

      A           B                  C
    Name      leavefrom          leaveupto
    Mathew    07-Mar-13          25-Mar-13
    john      12-Mar-13          15-Mar-13
    kerry     18-Apr-13          25-Apr-13
    Mikes     27-Jun-13          03-Jul-13

，我有一个单元格 C10 ，我在其中提供日期值.如果我提供的是13年3月14日，那么它应该显示名称"john"和"Mathew"，如果我提供的是13年4月19日，它应该显示"kerry"，依此类推...基本上，如果我提供日期值它应该找到在该特定日期休假的姓名.

and I have a cell C10 where I am providing a date value. If I am providing 14-mar-13 then it should show the name "john" and "Mathew", if I provide 19-Apr-13 it should show "kerry", etc... Basically, if I provide a date value it should find the names who are on leave on that particular date.

有人可以帮助我找到正确的公式吗?

Can someone help me find the correct formula?

推荐答案

由于您暗示可能需要返回多个名称，因此建议使用高级筛选器".查看帮助以了解如何进行设置.但是，假设您将日期输入到单元格G1中.您的条件范围"可能看起来像(显示公式，而不显示这些单元格中显示的结果)

Since you imply you might need to return multiple names, I would suggest the Advanced Filter. Look at HELP for how to set it up. But assume that you enter the date into cell G1. Your Criteria Range might look like (showing the formulas and not the results that show in those cells)

leavefrom       leaveupto
 ="<="&D1        =">="&D1

如果需要更多帮助，请发回邮件

Post back if you need more help

这里是使用高级筛选器"的图片.请注意对话框中复制到另一个位置"的选项，以及复制到"的地址

Here's a picture of using the Advanced Filter. Note the options in the Dialog Box to "Copy to another location", and the address to "copy to"

如果您要这样做，可以开发宏代码以自动更新高级过滤器.首先，请确保过滤器满足您的要求.

One can develop macro code to automate the updating of the advanced filter, if this is what you want to do. First make sure the filter does what you require.

这是您的用户更改日期后要运行的宏代码.看看是否适合您.您可能需要针对特定​​情况对其进行修改.

Here is Macro Code to run after the date has been changed by your user. See if it works for you. You will likely have to modify it for your specific situation.

如果可以，您可以设置一个按钮来运行它.如果还行，您可以开发代码以通过适当的事件触发它，以完全自动化事情.

If it works, you can set up a button to run it. If that also works, you can develop code to trigger it by an appropriate Event, to completely automate things.

Option Explicit
Sub LeaveList()
    Dim rDateCheck As Range
    Dim rSrc As Range
    Dim rCriteria As Range
    Dim rRes As Range

Set rDateCheck = Range("G1") 'or wherever you have the date
    If Not IsDate(rDateCheck) Then
        MsgBox "You MUST enter a Date!"
    Exit Sub
    End If
Set rRes = Range("I1") 'or wherever you want the results
    rRes.Resize(columnsize:=3).EntireColumn.Clear

With Cells
    Set rSrc = .Find(what:="Name", after:=Cells(.Rows.Count, .Columns.Count), _
                LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, _
                searchdirection:=xlNext, MatchCase:=False)
    'assume LeaveTable separated from rest of sheet by at least one empty
    '   column and row
    Set rSrc = rSrc.CurrentRegion
End With

'Move Leave Table to leave room for Criteria Range
With rSrc
    If .Row < 5 Then _
        Range(.Rows(1), .Rows(5 - .Row)).Insert shift:=xlDown
End With

'Put Criteria Range above Table
Set rCriteria = Cells(1, rSrc.Column).Resize(2, 2)
    rCriteria.Interior.Color = vbYellow
    rCriteria(1, 1) = rSrc(1, 2)
    rCriteria(1, 2) = rSrc(1, 3)
    rCriteria(2, 1).Formula = "=""<="" & " & rDateCheck.Address
    rCriteria(2, 2).Formula = "="">="" & " & rDateCheck.Address

   rSrc.AdvancedFilter xlFilterCopy, rCriteria, rRes
End Sub

这篇关于如何在两个日期之间查找数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！