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

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


--
Zygan
------------------------------------------------------------------------
Zygan's Profile: http://www.excelforum.com/member.php...o&userid=34423
View this thread: http://www.excelforum.com/showthread...hreadid=548536

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,th at)
--
Don Guillett
SalesAid Software

"SamuelT" wrote in
message ...

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

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


--
SamuelT
------------------------------------------------------------------------
SamuelT's Profile: http://www.excelforum.com/member.php...o&userid=27501
View this thread: http://www.excelforum.com/showthread...hreadid=548536