If / And / Or...

[SamuelT]

Hi all,

So, I've got a worksheet with a bunch of projects and dates that relate to it in various columns. From this worksheet I have another that displays only those projects that have an opening date within two weeks of today using this formula:

=IF(AND('Programme (High Level)'!$J6<=Control!$D$2, 'Programme (High Level)'!$J6>=Control!$C$2),'Programme (High Level)'!A6,"")

Control!$C$2 is today's date
Control!$D$2 is the date in two weeks.
Programme (High Level)'!$J6 is the opening date of the project.

So this is working fine. Now, I've been asked to extend this function so that if ANY of the dates come within two weeks the project is displayed in my second worksheet.

I've started trying to add the OR function to implement this, but keep getting caught up in the syntax. So aswell as J6 - I also need to include dates in L6, N6, P6, Q6, S6, U6, W6 and X6.

I tried something like this:

=IF(OR(AND('Programme (High Level)'!$J6<=Control!$D$2, 'Programme (High Level)'!$J6>=Control!$C$2)), AND('Programme (High Level)'!$L6<=Control!$D$2, 'Programme (High Level)'!$L6>=Control!$C$2)), AND('Programme (High Level)'!$N6<=Control!$D$2, 'Programme (High Level)'!$N6>=Control!$C$2))...etc

This doesn't seem to be working though, and oh, how it hurts the eyes!

Can anyone suggest where I going wrong, or a simpler formula to get the result I need.

TIA,

SamuelT

[Zygan]

Just a question ,

let me know if i have this right you want to know if
TODAYS date is after start date
TODAYS date is less then "due" date

I am a little confused why you want to know if all the dates fall in the period and the result to lie in one cell because if that cell value came back true that all your dates are all in the "time" period then thats fine but if the result came back false one /some are outside the "time" period then you would still have to find which one it is ? in which case the formula ais no good.


hopefully if you still want that to be the case then this website will help you out http://www.mathworks.com/access/help...ink/app2a.html

have a look for your correct syntax error and the solution is there try checking the " ' may be a typo

hope this helps

zygan

[Don Guillett]

how about something like this to count
=sumproduct((a2:a22>=c2)*(a2:a22<d2))
then
=if(sumproduct((a2:a22>=c2)*(a2:a22<d2))>0,this,that)
--
Don Guillett
SalesAid Software
[email protected]
"SamuelT" <[email protected]> wrote in
message news:[email protected]...
>
> Hi all,
>
> So, I've got a worksheet with a bunch of projects and dates that relate
> to it in various columns. From this worksheet I have another that
> displays only those projects that have an opening date within two weeks
> of today using this formula:
>
> =IF(AND('Programme (High Level)'!$J6<=Control!$D$2, 'Programme (High
> Level)'!$J6>=Control!$C$2),'Programme (High Level)'!A6,"")
>
> Control!$C$2 is today's date
> Control!$D$2 is the date in two weeks.
> Programme (High Level)'!$J6 is the opening date of the project.
>
> So this is working fine. Now, I've been asked to extend this function
> so that if ANY of the dates come within two weeks the project is
> displayed in my second worksheet.
>
> I've started trying to add the OR function to implement this, but keep
> getting caught up in the syntax. So aswell as J6 - I also need to
> include dates in L6, N6, P6, Q6, S6, U6, W6 and X6.
>
> I tried something like this:
>
> =IF(OR(AND('Programme (High Level)'!$J6<=Control!$D$2, 'Programme (High
> Level)'!$J6>=Control!$C$2)), AND('Programme (High
> Level)'!$L6<=Control!$D$2, 'Programme (High
> Level)'!$L6>=Control!$C$2)), AND('Programme (High
> Level)'!$N6<=Control!$D$2, 'Programme (High
> Level)'!$N6>=Control!$C$2))...etc
>
> This doesn't seem to be working though, and oh, how it hurts the eyes!
>
> Can anyone suggest where I going wrong, or a simpler formula to get the
> result I need.
>
> TIA,
>
> SamuelT
>
>
> --
> SamuelT
> ------------------------------------------------------------------------
> SamuelT's Profile:
> http://www.excelforum.com/member.php...o&userid=27501
> View this thread: http://www.excelforum.com/showthread...hreadid=548536
>

[SamuelT]

Hi guys,

Thanks for getting back to me.

So - today's date is todays date: =TODAY()
And today's date is less than the due date.

All the projects have various phases, which fall over a three month period (or thereabouts). What I want to show is that whenever a due date for one of the phases is within two weeks of today, then it shows up on my 'Two Week Overview'. The formula will actually go in a number of cells - I just need to get it right and then drag it around!

My thinking behind using IF(OR(AND... was that I could say IF the date in this column OR this column OR this column is between x date AND y date (i.e. the two week period) then display the cell value.

Any more clarification, let me know.

Cheers,

SamuelT