Conditional Formatting question

[Helen Holubowicz]

Hi I have a list of dates that will be entered, i have already a formula
that will calculate say 2 years on from the date, but i need the conditional
formatting to change the font to show me the dates from a month in advance,
In the dialog box i have tried

1. =today
2. between =today and =today+31

but im getting intermittent results, can anyone help?

Ta

 

[Bob Phillips]

Try a formula of

=AND(A2>=TODAY()-31,A2<=TODAY())

where A2 is the cell in question

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Helen Holubowicz]

Bob, that didn't seem to work, though i tried applying it to the whole sheet

this is my layout:

Duration [a2] 2
[b2]
Helen [a3] 01/28/06
[b3]
Date Due [a4] =DATE(YEAR(B3)+B$2,MONTH(B3),DAY(B3)) [b4]

There are multiple names and dates.

maybe there is a shorter/easier formula to use too ?

Thanks


=DATE(YEAR(B3)+B$2,MONTH(B3),DAY(B3))

 

[Bob Phillips]

What cell are you trying to format?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

Bob, that didn't seem to work, though i tried applying it to the whole
sheet

this is my layout:

Duration [a2] 2
[b2]
Helen [a3] 01/28/06
[b3]
Date Due [a4] =DATE(YEAR(B3)+B$2,MONTH(B3),DAY(B3)) [b4]

There are multiple names and dates.

maybe there is a shorter/easier formula to use too ?

Thanks


=DATE(YEAR(B3)+B$2,MONTH(B3),DAY(B3))

Try a formula of

=AND(A2>=TODAY()-31,A2<=TODAY())

where A2 is the cell in question

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Helen Holubowicz]

Hi Songbear

Thanks as soon as i get a chance Ill try this but in the meantime, can I
apply the conditional formatting to the whole worksheet, and not just a
single cell, in case a a date is calculated.. and it is in the range...

Thanks

Hi Bob and Hellen!
to test what I was doing, i created this data in a clean worksheet:
b1 2/2/2007
b2 2
b3 1/5/2005
b4 1/5/2007 1/2/2007 3

in formula view:
b1 =TODAY()+31
b2 2
b3 38357
b4/c4/d4 =DATE(YEAR(B3)+B$2,MONTH(B3),DAY(B3)) =TODAY() =B4-C4


I then added three conditional formats to B4:

Conditional Formats for "b4"

Cell is Yellow (Due within 31 days)
Formula is =AND(B4<=TODAY()+31,B4>=TODAY()+15)

Cell is Orange (Due within 15 days)
Formula is =AND(B4<=TODAY()+15,B4>=TODAY())

Cell is Red (overdue)
Cell Value Is less than =TODAY()

This seems to work, and it gives you a 15 day warning and a 31 day warning,
as well as an overdue warning.
Is that what you were looking for?
Please let us know if any of this has helped.
Thanks
Doug

What cell are you trying to format?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

Bob, that didn't seem to work, though i tried applying it to the whole
sheet

this is my layout:

Duration [a2] 2
[b2]
Helen [a3] 01/28/06
[b3]
Date Due [a4] =DATE(YEAR(B3)+B$2,MONTH(B3),DAY(B3)) [b4]

There are multiple names and dates.

maybe there is a shorter/easier formula to use too ?

Thanks


=DATE(YEAR(B3)+B$2,MONTH(B3),DAY(B3))
Try a formula of

=AND(A2>=TODAY()-31,A2<=TODAY())

where A2 is the cell in question

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


Hi I have a list of dates that will be entered, i have already a
formula
that will calculate say 2 years on from the date, but i need the
conditional
formatting to change the font to show me the dates from a month in
advance,
In the dialog box i have tried

1. =today
2. between =today and =today+31

but im getting intermittent results, can anyone help?

Ta

 

[Bob Phillips]

Yes, select all the target cells, and when applying CF, make sure you refer
to the first of the selected cells.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

Hi Songbear

Thanks as soon as i get a chance Ill try this but in the meantime, can I
apply the conditional formatting to the whole worksheet, and not just a
single cell, in case a a date is calculated.. and it is in the range...

Thanks

Hi Bob and Hellen!
to test what I was doing, i created this data in a clean worksheet:
b1 2/2/2007
b2 2
b3 1/5/2005
b4 1/5/2007 1/2/2007 3

in formula view:
b1 =TODAY()+31
b2 2
b3 38357
b4/c4/d4 =DATE(YEAR(B3)+B$2,MONTH(B3),DAY(B3)) =TODAY() =B4-C4


I then added three conditional formats to B4:

Conditional Formats for "b4"

Cell is Yellow (Due within 31 days)
Formula is =AND(B4<=TODAY()+31,B4>=TODAY()+15)

Cell is Orange (Due within 15 days)
Formula is =AND(B4<=TODAY()+15,B4>=TODAY())

Cell is Red (overdue)
Cell Value Is less than =TODAY()

This seems to work, and it gives you a 15 day warning and a 31 day warning,
as well as an overdue warning.
Is that what you were looking for?
Please let us know if any of this has helped.
Thanks
Doug

What cell are you trying to format?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


Bob, that didn't seem to work, though i tried applying it to the
whole
sheet

this is my layout:

Duration [a2] 2
[b2]
Helen [a3] 01/28/06
[b3]
Date Due [a4] =DATE(YEAR(B3)+B$2,MONTH(B3),DAY(B3)) [b4]

There are multiple names and dates.

maybe there is a shorter/easier formula to use too ?

Thanks


=DATE(YEAR(B3)+B$2,MONTH(B3),DAY(B3))
Try a formula of

=AND(A2>=TODAY()-31,A2<=TODAY())

where A2 is the cell in question

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


Hi I have a list of dates that will be entered, i have already a
formula
that will calculate say 2 years on from the date, but i need the
conditional
formatting to change the font to show me the dates from a month in
advance,
In the dialog box i have tried

1. =today
2. between =today and =today+31

but im getting intermittent results, can anyone help?

Ta

 

[Helen Holubowicz]

FIRE TRAINING FOOD + HYGIENE CPR
DURATION 2 1 1
helen 10/12/2006 01/28/06 12/12/06
date Due December 10, 2008 January 28, 2007 December 12, 2007
Piers 30/12/2005 10/10/2004 01/01/2006
Date Due December 30, 2007 October 10, 2005 January 1, 2007


Hi

Please can you clarify what you mean by my above sample of my spreadsheet,
It is very difficult to explain things sometimes

, I would like to apply CF to the whole ws, so that if the training Date Due
is 31 days to go, or 15, or overdue it will change font colour.

Thanks

Hi Songbear

Thanks as soon as i get a chance Ill try this but in the meantime, can I
apply the conditional formatting to the whole worksheet, and not just a
single cell, in case a a date is calculated.. and it is in the range...

Thanks

Hi Bob and Hellen!
to test what I was doing, i created this data in a clean worksheet:
b1 2/2/2007
b2 2
b3 1/5/2005
b4 1/5/2007 1/2/2007 3

in formula view:
b1 =TODAY()+31
b2 2
b3 38357
b4/c4/d4 =DATE(YEAR(B3)+B$2,MONTH(B3),DAY(B3)) =TODAY() =B4-C4


I then added three conditional formats to B4:

Conditional Formats for "b4"

Cell is Yellow (Due within 31 days)
Formula is =AND(B4<=TODAY()+31,B4>=TODAY()+15)

Cell is Orange (Due within 15 days)
Formula is =AND(B4<=TODAY()+15,B4>=TODAY())

Cell is Red (overdue)
Cell Value Is less than =TODAY()

This seems to work, and it gives you a 15 day warning and a 31 day warning,
as well as an overdue warning.
Is that what you were looking for?
Please let us know if any of this has helped.
Thanks
Doug

What cell are you trying to format?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


Bob, that didn't seem to work, though i tried applying it to the whole
sheet

this is my layout:

Duration [a2] 2
[b2]
Helen [a3] 01/28/06
[b3]
Date Due [a4] =DATE(YEAR(B3)+B$2,MONTH(B3),DAY(B3)) [b4]

There are multiple names and dates.

maybe there is a shorter/easier formula to use too ?

Thanks


=DATE(YEAR(B3)+B$2,MONTH(B3),DAY(B3))
Try a formula of

=AND(A2>=TODAY()-31,A2<=TODAY())

where A2 is the cell in question

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


Hi I have a list of dates that will be entered, i have already a
formula
that will calculate say 2 years on from the date, but i need the
conditional
formatting to change the font to show me the dates from a month in
advance,
In the dialog box i have tried

1. =today
2. between =today and =today+31

but im getting intermittent results, can anyone help?

Ta

 

[Helen Holubowicz]

Right,

I think i have the hang of it now.

Thanks very much for your help

FIRE TRAINING FOOD + HYGIENE CPR
DURATION 2 1 1
helen 10/12/2006 01/28/06 12/12/06
date Due December 10, 2008 January 28, 2007 December 12, 2007
Piers 30/12/2005 10/10/2004 01/01/2006
Date Due December 30, 2007 October 10, 2005 January 1, 2007


Hi

Please can you clarify what you mean by my above sample of my spreadsheet,
It is very difficult to explain things sometimes

, I would like to apply CF to the whole ws, so that if the training Date Due
is 31 days to go, or 15, or overdue it will change font colour.

Thanks

Hi Songbear

Thanks as soon as i get a chance Ill try this but in the meantime, can I
apply the conditional formatting to the whole worksheet, and not just a
single cell, in case a a date is calculated.. and it is in the range...

Thanks

Hi Bob and Hellen!
to test what I was doing, i created this data in a clean worksheet:
b1 2/2/2007
b2 2
b3 1/5/2005
b4 1/5/2007 1/2/2007 3

in formula view:
b1 =TODAY()+31
b2 2
b3 38357
b4/c4/d4 =DATE(YEAR(B3)+B$2,MONTH(B3),DAY(B3)) =TODAY() =B4-C4


I then added three conditional formats to B4:

Conditional Formats for "b4"

Cell is Yellow (Due within 31 days)
Formula is =AND(B4<=TODAY()+31,B4>=TODAY()+15)

Cell is Orange (Due within 15 days)
Formula is =AND(B4<=TODAY()+15,B4>=TODAY())

Cell is Red (overdue)
Cell Value Is less than =TODAY()

This seems to work, and it gives you a 15 day warning and a 31 day warning,
as well as an overdue warning.
Is that what you were looking for?
Please let us know if any of this has helped.
Thanks
Doug



:

What cell are you trying to format?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


Bob, that didn't seem to work, though i tried applying it to the whole
sheet

this is my layout:

Duration [a2] 2
[b2]
Helen [a3] 01/28/06
[b3]
Date Due [a4] =DATE(YEAR(B3)+B$2,MONTH(B3),DAY(B3)) [b4]

There are multiple names and dates.

maybe there is a shorter/easier formula to use too ?

Thanks


=DATE(YEAR(B3)+B$2,MONTH(B3),DAY(B3))
Try a formula of

=AND(A2>=TODAY()-31,A2<=TODAY())

where A2 is the cell in question

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


Hi I have a list of dates that will be entered, i have already a
formula
that will calculate say 2 years on from the date, but i need the
conditional
formatting to change the font to show me the dates from a month in
advance,
In the dialog box i have tried

1. =today
2. between =today and =today+31

but im getting intermittent results, can anyone help?

Ta