How to find data between two dates

Question

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

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?

Answer 1

If Name is in A1 then:

=IF(AND(C$10>=B2,C$10<=C2),A2,"")

in D2 and copied down to suit may serve.

Answer 2

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