Advanced Filter Problem
 

Hi

I am not sure if Advanced Filter is my answer but I have the following
problem which I am trying to resolve.

I have a worksheet that lists equipment we have hired to clients. It is
laid out as:
Job No | Start Date | End Date | No of days (a formula calculates this) |
Gear1 (no hired) | Gear 2 etc.

We have 19 pieces of gear that could potentially be hired.

I would like to extract for a date range what has been hired e.g. from
20/3/09 - 19/4/09. In my test data I know I have more than one entry that
should show up but only the first entry shows.

My criteria is:

Start Date Start Date End Date End Date
=20/3/09 <=19/4/09 =20/3/09 <=19/4/09

Can anyone help as to what I am doing wrong?

Thanks

Advanced Filter Problem
 

Try this..assuming you have start date and end date in ColB and ColC

=SUMPRODUCT(--(B1:B100=DATE(2009,3,20)),--(B1:B100<DATE(2009,4,19)),--(C1:C100=DATE(2009,3,20)),--(C1:C100<DATE(2009,4,19)))


If this post helps click Yes
---------------
Jacob Skaria


"Mustang" wrote:

Hi

I am not sure if Advanced Filter is my answer but I have the following
problem which I am trying to resolve.

I have a worksheet that lists equipment we have hired to clients. It is
laid out as:
Job No | Start Date | End Date | No of days (a formula calculates this) |
Gear1 (no hired) | Gear 2 etc.

We have 19 pieces of gear that could potentially be hired.

I would like to extract for a date range what has been hired e.g. from
20/3/09 - 19/4/09. In my test data I know I have more than one entry that
should show up but only the first entry shows.

My criteria is:

Start Date Start Date End Date End Date
=20/3/09 <=19/4/09 =20/3/09 <=19/4/09

Can anyone help as to what I am doing wrong?

Thanks

Advanced Filter Problem
 

Hi

I assume you want to see all the items on hire in that period.

Therefore if the the start dates are in column a and the end dates in column
b then add a column in say c with a test formula:

=AND(A6<=$B$2,B6=$A$2)

A2 is the start date
b2 is the end date

and then do a autofilter with criteria on column c = TRUE.

HTH
--
Hope this helps
Martin Fishlock, www.nyfconsultants.com, Wolverhampton, UK
Please do not forget to rate this reply.


"Mustang" wrote:

Hi

I am not sure if Advanced Filter is my answer but I have the following
problem which I am trying to resolve.

I have a worksheet that lists equipment we have hired to clients. It is
laid out as:
Job No | Start Date | End Date | No of days (a formula calculates this) |
Gear1 (no hired) | Gear 2 etc.

We have 19 pieces of gear that could potentially be hired.

I would like to extract for a date range what has been hired e.g. from
20/3/09 - 19/4/09. In my test data I know I have more than one entry that
should show up but only the first entry shows.

My criteria is:

Start Date Start Date End Date End Date
=20/3/09 <=19/4/09 =20/3/09 <=19/4/09

Can anyone help as to what I am doing wrong?

Thanks

Advanced Filter Problem
 

Thank you for your response. I dont think I explained myself very well.

What I am trying to achieve is to filter out entries for a certain date
range to another sheet which I will then query. So for each date I could
have 4 planks, 90 trusses etc on hire.

SUMPRODUCT does not seem to give me what I need.

Thanks

"Jacob Skaria" wrote:

Try this..assuming you have start date and end date in ColB and ColC

=SUMPRODUCT(--(B1:B100=DATE(2009,3,20)),--(B1:B100<DATE(2009,4,19)),--(C1:C100=DATE(2009,3,20)),--(C1:C100<DATE(2009,4,19)))


If this post helps click Yes
---------------
Jacob Skaria


"Mustang" wrote:

Hi

I am not sure if Advanced Filter is my answer but I have the following
problem which I am trying to resolve.

I have a worksheet that lists equipment we have hired to clients. It is
laid out as:
Job No | Start Date | End Date | No of days (a formula calculates this) |
Gear1 (no hired) | Gear 2 etc.

We have 19 pieces of gear that could potentially be hired.

I would like to extract for a date range what has been hired e.g. from
20/3/09 - 19/4/09. In my test data I know I have more than one entry that
should show up but only the first entry shows.

My criteria is:

Start Date Start Date End Date End Date
=20/3/09 <=19/4/09 =20/3/09 <=19/4/09

Can anyone help as to what I am doing wrong?

Thanks