Help with Excle Formula
 

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
--
Dave Eade

Help with Excle Formula
 

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" wrote:

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
--
Dave Eade

Help with Excle Formula
 

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

--
Dave Eade
EPCS Ltd
www.EPCS.me.uk



"John Moore" wrote:

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" wrote:

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
--
Dave Eade

Help with Excle Formula
 

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

"Dave Eade" wrote:

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

--
Dave Eade
EPCS Ltd
www.EPCS.me.uk



"John Moore" wrote:

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" wrote:

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
--
Dave Eade

Help with Excle Formula
 

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" wrote in message
...
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

--
Dave Eade
EPCS Ltd
www.EPCS.me.uk



"John Moore" wrote:

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" wrote:

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
--
Dave Eade

Help with Excle Formula
 

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
EPCS Ltd
www.EPCS.me.uk



"John Moore" wrote:

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

"Dave Eade" wrote:

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

--
Dave Eade
EPCS Ltd
www.EPCS.me.uk



"John Moore" wrote:

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" wrote:

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
--
Dave Eade

Help with Excle Formula
 

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

"Dave Eade" wrote in message
...
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
--
Dave Eade

Help with Excle Formula
 

Ok Dave, glad I could help

"Dave Eade" wrote:

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
EPCS Ltd
www.EPCS.me.uk



"John Moore" wrote:

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

"Dave Eade" wrote:

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

--
Dave Eade
EPCS Ltd
www.EPCS.me.uk



"John Moore" wrote:

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" wrote:

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
--
Dave Eade

Help with Excle Formula
 

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



"David Biddulph" wrote:

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

"Dave Eade" wrote in message
...
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
--
Dave Eade

Help with Excle Formula
 

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

"Dave Eade" wrote in message
...
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
EPCS Ltd
www.EPCS.me.uk



"John Moore" wrote:

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

"Dave Eade" wrote:

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

--
Dave Eade
EPCS Ltd
www.EPCS.me.uk



"John Moore" wrote:

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" wrote:

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
--
Dave Eade

Help with Excle Formula
 

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

"Dave Eade" wrote in message
...
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



"David Biddulph" wrote:

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

"Dave Eade" wrote in message
...
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
--
Dave Eade

Help with Excle Formula
 

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

Regards,
Fred.

"Dave Eade" wrote in message
...
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



"David Biddulph" wrote:

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

"Dave Eade" wrote in message
...
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
--
Dave Eade