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.

Hi Gayla,

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

on edit mode : press C-S-E.

Note when all referred cells are blank , result is 1.

regards,
driller

--
*****
birds of the same feather flock together..



"Gayla" wrote:

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.

=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,""))
--
David Biddulph

"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.

Hi David,
thanks, i missed the T3 by K3.

regards,
driller
--
*****
birds of the same feather flock together..



"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,""))
--
David Biddulph

"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.

Thanks for the help but I am getting a value error and when reading the
formula I am not sure it is doing exactly what I am needing. See if this
helps.

IF((AND($I$5="",$J$5="")
I have four columns where I could enter two sets of dates, planned and
actual. Prior to the project starting I will enter dates into G and H (I and
J will be blank at this time), once the project starts I will fill in the
dates into columns I and J.
G - Planned Start
H - Planned End
I - Actual Start
J - Actual End


In columns K thru BJ I have two sets of dates (two sets per column) that
represent one week based off the yearly calender, for example:
K3 - 01/01/07, K2 - 01/07/07
L3 - 01/08/07, L2 - 01/14/07
etc...

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,"")

I want my spreadsheet based off the dates I enter into column G and H row 5
(prior to project start) to check to see which date range (columns K-BJ) it
falls within, whichever column that it falls within, I want it to enter a 1
on that same row (5).

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,"")

If after the project starts I want it to do the same thing again but with
dates I enter into I and J columns, ignoring G and H dates.

Thanks for your help.

"driller" wrote:

Hi Gayla,

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

on edit mode : press C-S-E.

Note when all referred cells are blank , result is 1.

regards,
driller

--
*****
birds of the same feather flock together..



"Gayla" wrote:

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.

Hi Gayla,

try to replace the "OR" with "AND".
Press control-shift-enter
copy and paste on the same row - to the right from "K5"....only

regards to your schedule,
driller
--
*****
birds of the same feather flock together..



"Gayla" wrote:


Thanks for the help but I am getting a value error and when reading the
formula I am not sure it is doing exactly what I am needing. See if this
helps.

IF((AND($I$5="",$J$5="")
I have four columns where I could enter two sets of dates, planned and
actual. Prior to the project starting I will enter dates into G and H (I and
J will be blank at this time), once the project starts I will fill in the
dates into columns I and J.
G - Planned Start
H - Planned End
I - Actual Start
J - Actual End


In columns K thru BJ I have two sets of dates (two sets per column) that
represent one week based off the yearly calender, for example:
K3 - 01/01/07, K2 - 01/07/07
L3 - 01/08/07, L2 - 01/14/07
etc...

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,"")

I want my spreadsheet based off the dates I enter into column G and H row 5
(prior to project start) to check to see which date range (columns K-BJ) it
falls within, whichever column that it falls within, I want it to enter a 1
on that same row (5).

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,"")

If after the project starts I want it to do the same thing again but with
dates I enter into I and J columns, ignoring G and H dates.

Thanks for your help.

"driller" wrote:

Hi Gayla,

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

on edit mode : press C-S-E.

Note when all referred cells are blank , result is 1.

regards,
driller

--
*****
birds of the same feather flock together..



"Gayla" wrote:

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.

Hi Gayla,

after few test, for switchable DATES. <orig start, orig end ; new start, new
end

=IF(AND($I$5="",$J$5="",AND(SUM(IF($G$5:$J$5=K3,1 ))=1,SUM(IF($G$5:$J$5<=K2,1))=1)),1,IF(AND($I$5< "",$J$5<"",AND(SUM(IF($G$5:$J$5=K3,1))=1,SUM(I F($G$5:$J$5<=K2,1))=1)),1,""))

dont forget to press control-shift-enter.
then copy on the same row, may be nice if u can place the colors..as well.

regards,
driller



--
*****
birds of the same feather flock together..



"Gayla" wrote:


Thanks for the help but I am getting a value error and when reading the
formula I am not sure it is doing exactly what I am needing. See if this
helps.

IF((AND($I$5="",$J$5="")
I have four columns where I could enter two sets of dates, planned and
actual. Prior to the project starting I will enter dates into G and H (I and
J will be blank at this time), once the project starts I will fill in the
dates into columns I and J.
G - Planned Start
H - Planned End
I - Actual Start
J - Actual End


In columns K thru BJ I have two sets of dates (two sets per column) that
represent one week based off the yearly calender, for example:
K3 - 01/01/07, K2 - 01/07/07
L3 - 01/08/07, L2 - 01/14/07
etc...

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,"")

I want my spreadsheet based off the dates I enter into column G and H row 5
(prior to project start) to check to see which date range (columns K-BJ) it
falls within, whichever column that it falls within, I want it to enter a 1
on that same row (5).

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,"")

If after the project starts I want it to do the same thing again but with
dates I enter into I and J columns, ignoring G and H dates.

Thanks for your help.

"driller" wrote:

Hi Gayla,

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

on edit mode : press C-S-E.

Note when all referred cells are blank , result is 1.

regards,
driller

--
*****
birds of the same feather flock together..



"Gayla" wrote:

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.

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,""))
--
David Biddulph

"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.

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.
--
David Biddulph

"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,""))
--
David Biddulph

"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.

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.

Hi Gayla,

try this array formula on cell K5.

IF(AND($I5="",$J5="",AND(SUM(IF($G5:$H5=k$3,1))= 1,SUM(IF($G5:$H5<=k$2,1))=1)),1,IF(AND($I5<"",$J 5<"",AND(SUM(IF($I5:$J5=k$3,1))=1,SUM(IF($I5:$J 5<=k$2,1))=1)),1,""))

copy the above line to cell k5, type "=" , then press ctrl+shift+enter.
then
copy K5 until BJ5,
then
you can copy K5:BJ5 downward for another set of schedule..

Note: row 3 and row 2, contains the common reference for start and finish


assumption:
In columns K thru BJ I have two sets of dates (two sets per column) that
represent one week based off the yearly calender, for example:
K3 - 01/01/07, K2 - 01/07/07
L3 - 01/08/07, L2 - 01/14/07

IF((AND($I$5="",$J$5="")
I have four columns where I could enter two sets of dates, planned and
actual. Prior to the project starting I will enter dates into G and H (I and
J will be blank at this time), once the project starts I will fill in the
dates into columns I and J.
G - Planned Start
H - Planned End
I - Actual Start
J - Actual End

looking for your success...

regards,
driller
--
*****
birds of the same feather flock together..

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.

Hi Gayla,

This puts a "1" in all of the weeks the resource is scheduled or working on
a project.

=IF(AND(IF($I4="",K$2=$G4,K$2=$I4),IF($J4="",K$3 <=$H4,K$3<$J4)),1,"")

where I4 = Actual Start
K2 = Week Start Date
G4 = Plan Start
J4 = Actual End
K3 = Week End Date
H4 = Plan End

G H I J K
row 2 1/7/2007
row 3 Plan_St Plan_End Actual_St Actual_End 1/1/2007
row 4 1/9/2007 3/4/2008 1/15/2007 3/10/2008
row 5 2/6/2007 3/27/2007 2/18/2007 5/8/2007


"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.

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.