Dates within range

[sabi.kiss]

Hi guys,

I've got a date range starting with 27/6/11, finishing with 24/7/11.
I've got multiple entries on employees taking leave. So, Column A would be Employee Name, Column B Leave start date & Column C Leave finish date. Note: might have duplicate entries (same employee taking leave on different occasions).
I would need a list of employees who are taking leave within the set range (27/6/11-24/7/11).

Thanks,
Sabi

 

[Trevor G]

Surely this is a simple filter option, selecting the column and then filter and between your dates

If this is more complicated consider uploading a sample via Excel Jeannie or another HTML converter which can be found here.

 

[sabi.kiss]

Thanks to Sandeep Warrier (ref thread 518138) it's all fixed. Love your work Sandeep.
Cheers,
Sabi

 

[markmzz]

Sabi.kiss,

I know you've found a solution to your problem, but look at this another way to solve the same problem using the Advanced Filter feature in Excel. Do the following (for Excel 2007):

1) Create the layout below to use the Advanced Filter.


<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="FONT-WEIGHT: bold">Employee</TD><TD style="FONT-WEIGHT: bold">Start</TD><TD style="FONT-WEIGHT: bold">Finish</TD><TD style="TEXT-ALIGN: right"></TD><TD style="FONT-WEIGHT: bold">Leave</TD><TD style="FONT-WEIGHT: bold">Employee</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Name3</TD><TD style="TEXT-ALIGN: right">02/06/2011</TD><TD style="TEXT-ALIGN: right">02/06/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Name3</TD><TD style="TEXT-ALIGN: right">05/06/2011</TD><TD style="TEXT-ALIGN: right">05/06/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Name7</TD><TD style="TEXT-ALIGN: right">05/06/2011</TD><TD style="TEXT-ALIGN: right">05/06/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>Name10</TD><TD style="TEXT-ALIGN: right">08/06/2011</TD><TD style="TEXT-ALIGN: right">08/06/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>Name10</TD><TD style="TEXT-ALIGN: right">11/06/2011</TD><TD style="TEXT-ALIGN: right">11/06/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>Name5</TD><TD style="TEXT-ALIGN: right">11/06/2011</TD><TD style="TEXT-ALIGN: right">11/06/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD>Name2</TD><TD style="TEXT-ALIGN: right">11/06/2011</TD><TD style="TEXT-ALIGN: right">11/06/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD>Name1</TD><TD style="TEXT-ALIGN: right">14/06/2011</TD><TD style="TEXT-ALIGN: right">14/06/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD>Name8</TD><TD style="TEXT-ALIGN: right">15/06/2011</TD><TD style="TEXT-ALIGN: right">15/06/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD>Name9</TD><TD style="TEXT-ALIGN: right">19/06/2011</TD><TD style="TEXT-ALIGN: right">19/06/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD>Name6</TD><TD style="TEXT-ALIGN: right">20/06/2011</TD><TD style="TEXT-ALIGN: right">20/06/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD>Name3</TD><TD style="TEXT-ALIGN: right">20/06/2011</TD><TD style="TEXT-ALIGN: right">20/06/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD>Name10</TD><TD style="TEXT-ALIGN: right">23/06/2011</TD><TD style="TEXT-ALIGN: right">23/06/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="BACKGROUND-COLOR: #d8d8d8">Name9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">29/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">29/06/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="BACKGROUND-COLOR: #d8d8d8">Name4</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">29/06/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">29/06/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="BACKGROUND-COLOR: #d8d8d8">Name6</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">06/07/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">06/07/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD style="BACKGROUND-COLOR: #d8d8d8">Name4</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">08/07/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">08/07/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD style="BACKGROUND-COLOR: #d8d8d8">Name4</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">09/07/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">09/07/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD style="BACKGROUND-COLOR: #d8d8d8">Name6</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">10/07/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">10/07/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">21</TD><TD style="BACKGROUND-COLOR: #d8d8d8">Name8</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">14/07/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">14/07/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">22</TD><TD style="BACKGROUND-COLOR: #d8d8d8">Name5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">16/07/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">16/07/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">23</TD><TD style="BACKGROUND-COLOR: #d8d8d8">Name2</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">17/07/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">17/07/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">24</TD><TD style="BACKGROUND-COLOR: #d8d8d8">Name7</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">18/07/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">18/07/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">25</TD><TD style="BACKGROUND-COLOR: #d8d8d8">Name6</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">22/07/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">22/07/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">26</TD><TD style="BACKGROUND-COLOR: #d8d8d8">Name10</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">23/07/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">23/07/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">27</TD><TD style="BACKGROUND-COLOR: #d8d8d8">Name4</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">24/07/2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">24/07/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">28</TD><TD>Name1</TD><TD style="TEXT-ALIGN: right">27/07/2011</TD><TD style="TEXT-ALIGN: right">27/07/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">29</TD><TD>Name9</TD><TD style="TEXT-ALIGN: right">28/07/2011</TD><TD style="TEXT-ALIGN: right">28/07/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">30</TD><TD>Name7</TD><TD style="TEXT-ALIGN: right">28/07/2011</TD><TD style="TEXT-ALIGN: right">28/07/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">31</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">32</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD></TR></TBODY></TABLE>


2) Into cell E2, type the following formula:

=AND(C2>=DATE(2011,6,27),C2<=DATE(2011,7,24))

3) Become active the Data tab and choose Advanced (in the Sort & Filter group of this tab).

4) In the Advanced Filter dialog box, do the following:

-in the Action section, select Copy to another location

-in the List range box, type A1:C30 (source list range)

-in the Criteria range box, type E1:E2

-the Copy to box, type F1 (cell containing the title Employee)

-select the Unique records only check box

-and finally, click OK.

Markmzz