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.