Hi Gayla,

One correction:

where K3 = Week Start Date
K2 = Week End Date

"Gayla" wrote:

Thanks so much for your help. Sorry for the delayed response but having new
formula issues on another sheet and they have me under a time crunch.
The formula you provided is doing some of what I want it to. It was really
hard to express into words what I was trying to accomplish. See if this
helps.
Basically I am trying to schedule resources on projects - MS project is too
restricted for our business (same people on mulitple proj at same time). I am
using the ones to compute hours.

I have a schedule with projects and team members under each project
D E F G H
I J
Project Proj Type Assign Plan St Plan End Actual St
Actual End
XXX Inc Aud
XXX Inc Aud J.DOE 1/9/07 3/4/08
1/15/07 3/10/08
XXX Inc Aud B.SIMS 2/06/07 12/27/07 2/10/07
1/8/08

Rest of Sheet to the Right of columns D - J:

K L M N
O P
R2 1/7/07 1/14/07 1/21/07 1/28/07 2/4/07 2/11/07
........
R3 1/1/07 1/8/07 1/15/07 1/22/07 1/29/07 2/5/07
........

1 1
1 1

1
Each column represents a week, it goes out to column BJ through rest of
year.

I would like when I input in the planned start and end dates - it will put a
"1" under each column where the dates fall but I also would like it to put a
"1" in each column between the two columns where the dates fall.
When I enter in the actual start and actual finish I want it to ignore the
planned dates and use these dates to put the "1"'s in the appropriate columns.

The formula you gave does put the "1" in the column where the dates fall but
how do I also get it to put a "1" in each column between those two dates?


"David Biddulph" wrote:

Let's make a wild guess at what you might have been trying to do, Gayla.

What about
=IF(AND($I$5="",$J$5=""),IF(OR(AND($G$5=K3,$G$5<= K2),AND($H$5=K3,$H$5<=K2)),1,""),IF(OR(AND($I$5= K3,$I$5<=K2),AND($J$5=K3,$J$5<=K2)),1,""))
?
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
I don't know what you're trying to achieve, but your syntax looks wrong.

Your IF conditions asks whether AND($I$5="",$J$5="") is true.
If it is true, then your result of your formula is the logical result of
the OR function.
The first argument of the OR function is AND($G$5=K3,$G$5<=K2)
The second argument of the OR function is (AND($H$5=K3,$H$5<=K2),1,"")
which doesn't make sense as it doesn't say how you're trying to combine
the 1 and the "" with the result of the AND() function.
You've then closed your IF function without giving a result for the the
false condition, and the rest of the formula after that isn't valid
because you've already completed the IF function.

I guess you may have intended some later IF functions but you haven't
included them?

Two things you need to look at:
1 You need to ensure that you've got valid syntax for each function in
your formula.
2 You need to decide on the truth table for what you want as the result
for the various combinations of your inputs.

"Gayla" wrote in message
...
I am getting closer.
I changed it some because it was putting a 1 in every column from the
first
time a date constraint was met throughout the rest of the year. I
figured
out why, there should be a nested AND after the OR statement for the
first
two arguments, then the OR should pertain to the two nested AND
statements.
I also added a infront of the equal for the first argument in each set.
Please tell me what I am doing wrong.

=IF(AND($I$5="",$J$5=""),OR(AND($G$5=K3,$G$5<=K2) ,(AND($H$5=K3,$H$5<=K2),1,""))),OR(AND($I$5=K3,$ I$5<=K2),(AND($J$5=K3,$J$5<=K2),1,""))))

Thanks.
"David Biddulph" wrote:

=IF(AND($I$5="",$J$5=""),IF(OR($G$5=K3,$G$5<=K2),1 ,IF(OR($H$5=K3,$H$5<=K2),1,"")),IF(OR($I$5=T3,$I$5 <=K2),1,IF(OR($J$5=K3,$J$5<=K2),1,"")))
or simplify it to
=IF(AND($I$5="",$J$5=""),IF(OR($G$5=K3,$G$5<=K2,$H $5=K3,$H$5<=K2),1,""),IF(OR($I$5=T3,$I$5<=K2,$J$5= K3,$J$5<=K2),1,""))

"Gayla" wrote in message
...
Here are the formulas I am trying to put together into one formula but
cannot
seem to get it right.

IF((AND($I$5="",$J$5="") IF TRUE THEN DO THE FOLLOWING CALCULATION:

SEE IF ONE OF THE FOLLOWING APPLY:
OR($G$5=K3,$G$5<=K2),1,""), OR($H$5=K3,$H$5<=K2),1,"")

IF FALSE DO THE FOLLOWING:

SEE IF ONE OF THE FOLLOWING APPLY:
OR($I$5=T3,$I$5<=K2),1,""), OR($J$5=K3,$J$5<=K2),1,"")

Any help will be appreciated.