Help with nesting IF statements

[mesteptoe]

I am working on a project calendar, i.e Weeks v. tasks

As a task is started I want to get the cell relevant to that task and that week to show "Y" and turn RED. I am using the following statement in each cell to do that,
=IF(AND(G8>=$E$21,G8<=$F$21),"Y","") where G8 is the current date, E21 is the date the task starts and F21 the finish date for the task. Now this works fine as long as a finish date for the task (F21) has been entered. Once the date (G8) exceeds the task completion date (F21), the cell is left blank as it should be.
But if the task has started, i.e. E21>0, but not completed, i.e. F21=0 the schedule is left completely blank - as expected. But I need to show RED coloured cells to show the task is in progress.
Somehow I need to link another formula to the
=IF(AND(G8>=$E$21,G8<=$F$21),"Y","")
i.e. =IF(AND(G8>=$E$21,$F$21=0),"Y","") such that two sets of criteria are checked out. Is it possible to link the two statements together with an OR statement? If so, how do I do that?

Thanks for your help.

Regards

Graham

 

[Andrew Poulsom]

Is it?

=IF(AND(G8>=$E$21,OR(G8<=$F$21,$F$21=0)),"Y","")

 

[mesteptoe]

Unfortunately not, because it colours every cell RED even past the current date.

 

[Andrew Poulsom]

Sorry, I don't understand what you mean by "even past the current date". Where do you want the colouring to stop?

 

[mesteptoe]

Hi

The colouring cannot continue past the current date because you cannot undertake a task past the current date. You can programme to complete a task at a date in the future but actual progress can only be shgown to the current date. So, if the task has started and is ongoing at the current date then the cell is coloured red. But cells past the current date must be left blank - until such time as they are current.

Graham

 

[Andrew Poulsom]

Another possibility:

=IF(AND(G8>=$E$21,G8<=MAX($F$21,TODAY())),"Y","")

 

[mesteptoe]

Hi

Afraid not. If both E18 and F18 are blank, i.e. a task not even started, all the cells up to the current date are coloured RED. Should I E-mail you the file?

 

[Andrew Poulsom]

That's the first time you've mentioned that the cell in column E can be blank. Try:

=IF(AND(ISNUMBER($E$21),G8>=$E$21,G8<=MAX($F$21,TODAY())),"Y","")

 

[mesteptoe]

I think you've done it for me Andrew, although I needed to change MAX to MIN

=IF(AND(ISNUMBER($E$21),G8>=$E$21,G8<=MIN($F$21,TODAY())),"Y","")

Does that sound logical to you?

Thank you very much.

Regards

Graham

 

[Andrew Poulsom]

It may be right if you never want to colour beyond today.