Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel advanced filter problem | Excel Discussion (Misc queries) | |||
Problem with Advanced Filter Criteria | Excel Discussion (Misc queries) | |||
Advanced Filter problem | Excel Discussion (Misc queries) | |||
Problem using Advanced filter | Excel Discussion (Misc queries) | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions |