If / And / Or...

[SamuelT]

Hi all

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

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

Control!$C$2 is today's dat
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 functio
so that if ANY of the dates come within two weeks the project i
displayed in my second worksheet

I've started trying to add the OR function to implement this, but kee
getting caught up in the syntax. So aswell as J6 - I also need t
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 (Hig
Level)'!$J6>=Control!$C$2)), AND('Programme (Hig
Level)'!$L6<=Control!$D$2, 'Programme (Hig
Level)'!$L6>=Control!$C$2)), AND('Programme (Hig
Level)'!$N6<=Control!$D$2, 'Programme (Hig
Level)'!$N6>=Control!$C$2))...et

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 th
result I need

TIA

Samuel

 

[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/helpdesk/help/toolbox/exlink/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)

 

[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 mont
period (or thereabouts). What I want to show is that whenever a du
date for one of the phases is within two weeks of today, then it show
up on my 'Two Week Overview'. The formula will actually go in a numbe
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 dat
in this column OR this column OR this column is between x date AND
date (i.e. the two week period) then display the cell value.

Any more clarification, let me know.

Cheers,

Samuel