Help with Excle Formula

[Dave Eade]

Hi, I'm trying to write a formula to do the following.

1. I have a cell which is returning "todays date"/
2. I have a column of activities which each have a date due and a "Yes/No"
completed value.

I want to write a forula which tests whether the Due Date is in the Past and
the Completed Flag is Set to "No"

Once I have this Value I want to conditionally format the Description based
on this Vales.

And befire anyone says, I know I should use MS Project, but the client I'm
working doesn't want to use it and lots of people who have never used MSP
will need access to and read/write to the excel.

Hope someone can help, virtual beers are on me!

Cheers

 

[John Moore]

Dave, sounds like you need to us the IF statement ,,, something like this

=if(B2<A$1,"",NO).... assumption that todays date is in A1 and the task
completion date is in B2
then just conditional format to show whatever you want.

 

[Dave Eade]

Hi John,
Thanks for that.

I got about as far as the testing the date against "todays Date", what I
need to do is have multiple conditions:

I guess it read like

If B2 < A1 AND
C2 does not = "yes"
Then True
Else False

It's the combination bit I'm struggling with.

Thanks

 

[John Moore]

Hi Dave, I'm not sure I am understanding what you want to do next

 

[MartinW]

Hi Dave,

=IF(AND(B2<A1,C2<>"YES"), value if true,value if false)
is the syntax for the formula as you have stated it'

There is no need to use the IF function if you are using it in CF.
Something like =AND($B2<$A1, $C2<>"yes") should do for CF.
or =AND($B2<$A1, $C2="no")

HTH
Martin

 

[Dave Eade]

Hi John,

I think I've worked it out now.

I use the "AND" function and supply 2 arguements, if they are both true then
my field gets populated with True.

I can then format the text in the "description" cell, by using the True or
False value in the cell already calculated.

Thanks for your help.

 

[David Biddulph]

In Conditional Formatting, use Formula Is:
=AND(A2<TODAY(),B2="No")

 

[John Moore]

Ok Dave, glad I could help

Hi John,

I think I've worked it out now.

I use the "AND" function and supply 2 arguements, if they are both true then
my field gets populated with True.

I can then format the text in the "description" cell, by using the True or
False value in the cell already calculated.

Thanks for your help.

 

[Dave Eade]

Excellent! - that works better than my attempt - one last question - once
I've got it to work for Row2 - how do I copy down the column on the
spreadsheet - is this possible??

Thanks
--
Dave Eade

In Conditional Formatting, use Formula Is:
=AND(A2<TODAY(),B2="No")

 

[David Biddulph]

You don't need to go through that intermediate step of calculating a TRUE or
FALSE in a cell and using that in CF.
In CF, use Formula Is, rather than Cell Value Is, and put your formula
=AND(...,...)

 

[David Biddulph]

Use the Format painter button, or
Copy the original cell, select the further cells, & edit/ paste special/
format.
--
David Biddulph

Excellent! - that works better than my attempt - one last question - once
I've got it to work for Row2 - how do I copy down the column on the
spreadsheet - is this possible??

Thanks

 

[Fred Smith]

Right-drag the fill handle (the small box in the bottom right corner) down,
release button, choose Fill Formatting Only.

Regards,
Fred.

Excellent! - that works better than my attempt - one last question - once
I've got it to work for Row2 - how do I copy down the column on the
spreadsheet - is this possible??

Thanks