Hi,

I'd like to find out how to format an Excel 2002 worksheet to highlight the
current date (in column A) in green and then if it is overdue by more than 2
days to change to red but if the amount has been paid in full (shown in
column F) to revert back to white, please.

Many thanks,
vpschas
(UK)

Hi

Mark your range of data.
FormatConditional Formattinguse dropdown to select Formula Is
=AND(A1=TODAY(),ISBLANK(F1))
FormatPatternsGreen
Choose Add
Formula Is =AND(TODAY()-A12,ISBLANK(F1))
FormatPatternsRed

Change the second part of the AND statement to whatever would be
appropriate for what cell F would show is fully paid.

--
Regards

Roger Govier


"vpschas" wrote in message
...
Hi,

I'd like to find out how to format an Excel 2002 worksheet to
highlight the
current date (in column A) in green and then if it is overdue by more
than 2
days to change to red but if the amount has been paid in full (shown
in
column F) to revert back to white, please.

Many thanks,
vpschas
(UK)

Hi,

Sorry, I couldn't get this to work.

Thanks
vpschas
(UK)

"Roger Govier" wrote:

Hi

Mark your range of data.
FormatConditional Formattinguse dropdown to select Formula Is
=AND(A1=TODAY(),ISBLANK(F1))
FormatPatternsGreen
Choose Add
Formula Is =AND(TODAY()-A12,ISBLANK(F1))
FormatPatternsRed

Change the second part of the AND statement to whatever would be
appropriate for what cell F would show is fully paid.

--
Regards

Roger Govier


"vpschas" wrote in message
...
Hi,

I'd like to find out how to format an Excel 2002 worksheet to
highlight the
current date (in column A) in green and then if it is overdue by more
than 2
days to change to red but if the amount has been paid in full (shown
in
column F) to revert back to white, please.

Many thanks,
vpschas
(UK)

Can you confirm what you want to do?

If A1 contains today's date it should turn green if F1 is blank? Then when
more than 2 day's have passed from today and F1 is still blank show red,
otherwise white?

If this isn't what you require then please give more detail - e.g. what
might F1 contain, a figure, some words a blank......

For the above scenario use for condition 1

=(TODAY()-A12)*(F1="")*(A1<"")

red format

and for condition 2

=(TODAY()-A1-1)*(F1="")*(A1<"")





"vpschas" wrote:

Hi,

Sorry, I couldn't get this to work.

Thanks
vpschas
(UK)

"Roger Govier" wrote:

Hi

Mark your range of data.
FormatConditional Formattinguse dropdown to select Formula Is
=AND(A1=TODAY(),ISBLANK(F1))
FormatPatternsGreen
Choose Add
Formula Is =AND(TODAY()-A12,ISBLANK(F1))
FormatPatternsRed

Change the second part of the AND statement to whatever would be
appropriate for what cell F would show is fully paid.

--
Regards

Roger Govier


"vpschas" wrote in message
...
Hi,

I'd like to find out how to format an Excel 2002 worksheet to
highlight the
current date (in column A) in green and then if it is overdue by more
than 2
days to change to red but if the amount has been paid in full (shown
in
column F) to revert back to white, please.

Many thanks,
vpschas
(UK)

Not exactly,

Column A contains weekly dates.
Column E contains total amount due.
Column F contains Less Amount paid.
Column G shows balance carried forward to following week (if balance due in
Column E is paid in full in Column F, the cell in Column G is blank, if
still any amount owed, shows in currency).
Column E automatically updates to include any arrears from previous weeks
plus total amount due this week.

So what I'd like to be able to do is:

When Column A shows todays date, highlight in green, to indicate that it is
now due.

When todays date is 2 days past the due date in Column A , highlight in red,
to indicate that it is now in arrears.

Then no highlighting when balance paid in full or the next todays date is
reached as hopefully the green/red formatting will start again.

Hope this makes sense.

many thanks,
vpschas
(UK)


"daddylonglegs" wrote:

Can you confirm what you want to do?

If A1 contains today's date it should turn green if F1 is blank? Then when
more than 2 day's have passed from today and F1 is still blank show red,
otherwise white?

If this isn't what you require then please give more detail - e.g. what
might F1 contain, a figure, some words a blank......

For the above scenario use for condition 1

=(TODAY()-A12)*(F1="")*(A1<"")

red format

and for condition 2

=(TODAY()-A1-1)*(F1="")*(A1<"")





"vpschas" wrote:

Hi,

Sorry, I couldn't get this to work.

Thanks
vpschas
(UK)

"Roger Govier" wrote:

Hi

Mark your range of data.
FormatConditional Formattinguse dropdown to select Formula Is
=AND(A1=TODAY(),ISBLANK(F1))
FormatPatternsGreen
Choose Add
Formula Is =AND(TODAY()-A12,ISBLANK(F1))
FormatPatternsRed

Change the second part of the AND statement to whatever would be
appropriate for what cell F would show is fully paid.

--
Regards

Roger Govier


"vpschas" wrote in message
...
Hi,

I'd like to find out how to format an Excel 2002 worksheet to
highlight the
current date (in column A) in green and then if it is overdue by more
than 2
days to change to red but if the amount has been paid in full (shown
in
column F) to revert back to white, please.

Many thanks,
vpschas
(UK)

Hi

Compared with my earlier solution, Daddylonglegs has quite rightly
picked up that one also needs to test that there is a value in A1,
otherwise it will turn Red anyway.

However, the test (from what you now describe) should be against column
G rather than F, so just replace this in Daddylonglegs formula.
=(TODAY()-A12)*(G1="")*(A1<"")
=(TODAY()-A1-1)*(G1="")*(A1<"")

However, it sounds as though there is a formula calculation in G1
(=E1-F1) and whilst you say it shows as blank if the amount has been
paid in full, it may be showing 0 - but the format is set not to display
zero's.

If this is the case, then you would probably have to change the test in
both case from G1="" to G1=0

--
Regards

Roger Govier


"vpschas" wrote in message
...
Not exactly,

Column A contains weekly dates.
Column E contains total amount due.
Column F contains Less Amount paid.
Column G shows balance carried forward to following week (if balance
due in
Column E is paid in full in Column F, the cell in Column G is blank,
if
still any amount owed, shows in currency).
Column E automatically updates to include any arrears from previous
weeks
plus total amount due this week.

So what I'd like to be able to do is:

When Column A shows todays date, highlight in green, to indicate that
it is
now due.

When todays date is 2 days past the due date in Column A , highlight
in red,
to indicate that it is now in arrears.

Then no highlighting when balance paid in full or the next todays date
is
reached as hopefully the green/red formatting will start again.

Hope this makes sense.

many thanks,
vpschas
(UK)


"daddylonglegs" wrote:

Can you confirm what you want to do?

If A1 contains today's date it should turn green if F1 is blank? Then
when
more than 2 day's have passed from today and F1 is still blank show
red,
otherwise white?

If this isn't what you require then please give more detail - e.g.
what
might F1 contain, a figure, some words a blank......

For the above scenario use for condition 1

=(TODAY()-A12)*(F1="")*(A1<"")

red format

and for condition 2

=(TODAY()-A1-1)*(F1="")*(A1<"")





"vpschas" wrote:

Hi,

Sorry, I couldn't get this to work.

Thanks
vpschas
(UK)

"Roger Govier" wrote:

Hi

Mark your range of data.
FormatConditional Formattinguse dropdown to select Formula Is
=AND(A1=TODAY(),ISBLANK(F1))
FormatPatternsGreen
Choose Add
Formula Is =AND(TODAY()-A12,ISBLANK(F1))
FormatPatternsRed

Change the second part of the AND statement to whatever would be
appropriate for what cell F would show is fully paid.

--
Regards

Roger Govier


"vpschas" wrote in message
...
Hi,

I'd like to find out how to format an Excel 2002 worksheet to
highlight the
current date (in column A) in green and then if it is overdue
by more
than 2
days to change to red but if the amount has been paid in full
(shown
in
column F) to revert back to white, please.

Many thanks,
vpschas
(UK)

Hi Roger and daddylonglegs,

Thank you both for your help.

Yes you are correct in saying there is a formula calculation in G1 (=E1-F1)
and is set not to display zero's.

Sorry to be a nuisance, I feel incredibly stupid now as I still can't get
this to work.

Conditional formatting for cell A22
Condition 1=(TODAY()-A222)*(G22=0)*(A22<"") for RED highlighting
Condition 2=(TODAY()-A22-1)*(G22=0)*(A22<"") for GREEN highlighting

I've written it as above because A22 contains Saturday's date 2nd Sept, so
as today is 4th I hoped the date would show in Red as there are still an
outstanding balance to pay in G22 but the date is not highlighted with any
color? Any ideas please?

Also I like to know exactly why things happen, so can you please confirm
that I understand this correctly!

The first part of the formula is checking todays date and if A22 is greater
than 2 or greater than -1, you've already said that G22=0 is to check if zero
balance in G22 but can you please explain the last part of the formula
(A22<"") as I have no idea what this means?

Many thanks,
vpschas
(UK)
================================================== ==

"Roger Govier" wrote:

Hi

Compared with my earlier solution, Daddylonglegs has quite rightly
picked up that one also needs to test that there is a value in A1,
otherwise it will turn Red anyway.

However, the test (from what you now describe) should be against column
G rather than F, so just replace this in Daddylonglegs formula.
=(TODAY()-A12)*(G1="")*(A1<"")
=(TODAY()-A1-1)*(G1="")*(A1<"")

However, it sounds as though there is a formula calculation in G1
(=E1-F1) and whilst you say it shows as blank if the amount has been
paid in full, it may be showing 0 - but the format is set not to display
zero's.

If this is the case, then you would probably have to change the test in
both case from G1="" to G1=0

--
Regards

Roger Govier


"vpschas" wrote in message
...
Not exactly,

Column A contains weekly dates.
Column E contains total amount due.
Column F contains Less Amount paid.
Column G shows balance carried forward to following week (if balance
due in
Column E is paid in full in Column F, the cell in Column G is blank,
if
still any amount owed, shows in currency).
Column E automatically updates to include any arrears from previous
weeks
plus total amount due this week.

So what I'd like to be able to do is:

When Column A shows todays date, highlight in green, to indicate that
it is
now due.

When todays date is 2 days past the due date in Column A , highlight
in red,
to indicate that it is now in arrears.

Then no highlighting when balance paid in full or the next todays date
is
reached as hopefully the green/red formatting will start again.

Hope this makes sense.

many thanks,
vpschas
(UK)


"daddylonglegs" wrote:

Can you confirm what you want to do?

If A1 contains today's date it should turn green if F1 is blank? Then
when
more than 2 day's have passed from today and F1 is still blank show
red,
otherwise white?

If this isn't what you require then please give more detail - e.g.
what
might F1 contain, a figure, some words a blank......

For the above scenario use for condition 1

=(TODAY()-A12)*(F1="")*(A1<"")

red format

and for condition 2

=(TODAY()-A1-1)*(F1="")*(A1<"")





"vpschas" wrote:

Hi,

Sorry, I couldn't get this to work.

Thanks
vpschas
(UK)

"Roger Govier" wrote:

Hi

Mark your range of data.
FormatConditional Formattinguse dropdown to select Formula Is
=AND(A1=TODAY(),ISBLANK(F1))
FormatPatternsGreen
Choose Add
Formula Is =AND(TODAY()-A12,ISBLANK(F1))
FormatPatternsRed

Change the second part of the AND statement to whatever would be
appropriate for what cell F would show is fully paid.

--
Regards

Roger Govier


"vpschas" wrote in message
...
Hi,

I'd like to find out how to format an Excel 2002 worksheet to
highlight the
current date (in column A) in green and then if it is overdue
by more
than 2
days to change to red but if the amount has been paid in full
(shown
in
column F) to revert back to white, please.

Many thanks,
vpschas
(UK)

Hi

The reason is, the 4th of Sept is only 2 days after the 2nd. The test we
have set up, is for periods more than 2 days.
If you want to include 2 days, then the formula would need to be amended
to either
(TODAY()-A22=2)
or
(TODAY()-A221)

The test for
(A22<"")
is ensuring that A22 is not blank or empty. As far as Excel is
concerned, in a date calculation, it treats the blank entry as
01/01/1900 hence the above tests would always return values greater than
2 and empty cells would all be made Red by the conditional formula.

Incidentally the part of the formula that is set to
(TODAY()-A22-1)
could equally be written as
(TODAY()=A22)

--
Regards

Roger Govier


"vpschas" wrote in message
...
Hi Roger and daddylonglegs,

Thank you both for your help.

Yes you are correct in saying there is a formula calculation in G1
(=E1-F1)
and is set not to display zero's.

Sorry to be a nuisance, I feel incredibly stupid now as I still can't
get
this to work.

Conditional formatting for cell A22
Condition 1=(TODAY()-A222)*(G22=0)*(A22<"") for RED
highlighting
Condition 2=(TODAY()-A22-1)*(G22=0)*(A22<"") for GREEN
highlighting

I've written it as above because A22 contains Saturday's date 2nd
Sept, so
as today is 4th I hoped the date would show in Red as there are still
an
outstanding balance to pay in G22 but the date is not highlighted with
any
color? Any ideas please?

Also I like to know exactly why things happen, so can you please
confirm
that I understand this correctly!

The first part of the formula is checking todays date and if A22 is
greater
than 2 or greater than -1, you've already said that G22=0 is to check
if zero
balance in G22 but can you please explain the last part of the formula
(A22<"") as I have no idea what this means?

Many thanks,
vpschas
(UK)
================================================== ==

"Roger Govier" wrote:

Hi

Compared with my earlier solution, Daddylonglegs has quite rightly
picked up that one also needs to test that there is a value in A1,
otherwise it will turn Red anyway.

However, the test (from what you now describe) should be against
column
G rather than F, so just replace this in Daddylonglegs formula.
=(TODAY()-A12)*(G1="")*(A1<"")
=(TODAY()-A1-1)*(G1="")*(A1<"")

However, it sounds as though there is a formula calculation in G1
(=E1-F1) and whilst you say it shows as blank if the amount has been
paid in full, it may be showing 0 - but the format is set not to
display
zero's.

If this is the case, then you would probably have to change the test
in
both case from G1="" to G1=0

--
Regards

Roger Govier


"vpschas" wrote in message
...
Not exactly,

Column A contains weekly dates.
Column E contains total amount due.
Column F contains Less Amount paid.
Column G shows balance carried forward to following week (if
balance
due in
Column E is paid in full in Column F, the cell in Column G is
blank,
if
still any amount owed, shows in currency).
Column E automatically updates to include any arrears from previous
weeks
plus total amount due this week.

So what I'd like to be able to do is:

When Column A shows todays date, highlight in green, to indicate
that
it is
now due.

When todays date is 2 days past the due date in Column A ,
highlight
in red,
to indicate that it is now in arrears.

Then no highlighting when balance paid in full or the next todays
date
is
reached as hopefully the green/red formatting will start again.

Hope this makes sense.

many thanks,
vpschas
(UK)


"daddylonglegs" wrote:

Can you confirm what you want to do?

If A1 contains today's date it should turn green if F1 is blank?
Then
when
more than 2 day's have passed from today and F1 is still blank
show
red,
otherwise white?

If this isn't what you require then please give more detail - e.g.
what
might F1 contain, a figure, some words a blank......

For the above scenario use for condition 1

=(TODAY()-A12)*(F1="")*(A1<"")

red format

and for condition 2

=(TODAY()-A1-1)*(F1="")*(A1<"")





"vpschas" wrote:

Hi,

Sorry, I couldn't get this to work.

Thanks
vpschas
(UK)

"Roger Govier" wrote:

Hi

Mark your range of data.
FormatConditional Formattinguse dropdown to select Formula
Is
=AND(A1=TODAY(),ISBLANK(F1))
FormatPatternsGreen
Choose Add
Formula Is =AND(TODAY()-A12,ISBLANK(F1))
FormatPatternsRed

Change the second part of the AND statement to whatever would
be
appropriate for what cell F would show is fully paid.

--
Regards

Roger Govier


"vpschas" wrote in message
...
Hi,

I'd like to find out how to format an Excel 2002 worksheet
to
highlight the
current date (in column A) in green and then if it is
overdue
by more
than 2
days to change to red but if the amount has been paid in
full
(shown
in
column F) to revert back to white, please.

Many thanks,
vpschas
(UK)

Have amended formatting as advised and copied and format painted the
conditional formatting into all cells in Column A but now only all the
previous dates where the balance outstanding in Column G is zero are
highlighted Red.

Thanks,
vpschas
(UK)

"Roger Govier" wrote:

Hi

The reason is, the 4th of Sept is only 2 days after the 2nd. The test we
have set up, is for periods more than 2 days.
If you want to include 2 days, then the formula would need to be amended
to either
(TODAY()-A22=2)
or
(TODAY()-A221)

The test for
(A22<"")
is ensuring that A22 is not blank or empty. As far as Excel is
concerned, in a date calculation, it treats the blank entry as
01/01/1900 hence the above tests would always return values greater than
2 and empty cells would all be made Red by the conditional formula.

Incidentally the part of the formula that is set to
(TODAY()-A22-1)
could equally be written as
(TODAY()=A22)

--
Regards

Roger Govier


"vpschas" wrote in message
...
Hi Roger and daddylonglegs,

Thank you both for your help.

Yes you are correct in saying there is a formula calculation in G1
(=E1-F1)
and is set not to display zero's.

Sorry to be a nuisance, I feel incredibly stupid now as I still can't
get
this to work.

Conditional formatting for cell A22
Condition 1=(TODAY()-A222)*(G22=0)*(A22<"") for RED
highlighting
Condition 2=(TODAY()-A22-1)*(G22=0)*(A22<"") for GREEN
highlighting

I've written it as above because A22 contains Saturday's date 2nd
Sept, so
as today is 4th I hoped the date would show in Red as there are still
an
outstanding balance to pay in G22 but the date is not highlighted with
any
color? Any ideas please?

Also I like to know exactly why things happen, so can you please
confirm
that I understand this correctly!

The first part of the formula is checking todays date and if A22 is
greater
than 2 or greater than -1, you've already said that G22=0 is to check
if zero
balance in G22 but can you please explain the last part of the formula
(A22<"") as I have no idea what this means?

Many thanks,
vpschas
(UK)
================================================== ==

"Roger Govier" wrote:

Hi

Compared with my earlier solution, Daddylonglegs has quite rightly
picked up that one also needs to test that there is a value in A1,
otherwise it will turn Red anyway.

However, the test (from what you now describe) should be against
column
G rather than F, so just replace this in Daddylonglegs formula.
=(TODAY()-A12)*(G1="")*(A1<"")
=(TODAY()-A1-1)*(G1="")*(A1<"")

However, it sounds as though there is a formula calculation in G1
(=E1-F1) and whilst you say it shows as blank if the amount has been
paid in full, it may be showing 0 - but the format is set not to
display
zero's.

If this is the case, then you would probably have to change the test
in
both case from G1="" to G1=0

--
Regards

Roger Govier


"vpschas" wrote in message
...
Not exactly,

Column A contains weekly dates.
Column E contains total amount due.
Column F contains Less Amount paid.
Column G shows balance carried forward to following week (if
balance
due in
Column E is paid in full in Column F, the cell in Column G is
blank,
if
still any amount owed, shows in currency).
Column E automatically updates to include any arrears from previous
weeks
plus total amount due this week.

So what I'd like to be able to do is:

When Column A shows todays date, highlight in green, to indicate
that
it is
now due.

When todays date is 2 days past the due date in Column A ,
highlight
in red,
to indicate that it is now in arrears.

Then no highlighting when balance paid in full or the next todays
date
is
reached as hopefully the green/red formatting will start again.

Hope this makes sense.

many thanks,
vpschas
(UK)


"daddylonglegs" wrote:

Can you confirm what you want to do?

If A1 contains today's date it should turn green if F1 is blank?
Then
when
more than 2 day's have passed from today and F1 is still blank
show
red,
otherwise white?

If this isn't what you require then please give more detail - e.g.
what
might F1 contain, a figure, some words a blank......

For the above scenario use for condition 1

=(TODAY()-A12)*(F1="")*(A1<"")

red format

and for condition 2

=(TODAY()-A1-1)*(F1="")*(A1<"")





"vpschas" wrote:

Hi,

Sorry, I couldn't get this to work.

Thanks
vpschas
(UK)

"Roger Govier" wrote:

Hi

Mark your range of data.
FormatConditional Formattinguse dropdown to select Formula
Is
=AND(A1=TODAY(),ISBLANK(F1))
FormatPatternsGreen
Choose Add
Formula Is =AND(TODAY()-A12,ISBLANK(F1))
FormatPatternsRed

Change the second part of the AND statement to whatever would
be
appropriate for what cell F would show is fully paid.

--
Regards

Roger Govier


"vpschas" wrote in message
...
Hi,

I'd like to find out how to format an Excel 2002 worksheet
to
highlight the
current date (in column A) in green and then if it is
overdue
by more
than 2
days to change to red but if the amount has been paid in
full
(shown
in
column F) to revert back to white, please.

Many thanks,
vpschas
(UK)

Sorry
Of course it should be
(G22<0)
we are looking for the ones where there is an outstanding balance, not
the ones which are cleared.



--
Regards

Roger Govier


"vpschas" wrote in message
...
Have amended formatting as advised and copied and format painted the
conditional formatting into all cells in Column A but now only all the
previous dates where the balance outstanding in Column G is zero are
highlighted Red.

Thanks,
vpschas
(UK)

"Roger Govier" wrote:

Hi

The reason is, the 4th of Sept is only 2 days after the 2nd. The test
we
have set up, is for periods more than 2 days.
If you want to include 2 days, then the formula would need to be
amended
to either
(TODAY()-A22=2)
or
(TODAY()-A221)

The test for
(A22<"")
is ensuring that A22 is not blank or empty. As far as Excel is
concerned, in a date calculation, it treats the blank entry as
01/01/1900 hence the above tests would always return values greater
than
2 and empty cells would all be made Red by the conditional formula.

Incidentally the part of the formula that is set to
(TODAY()-A22-1)
could equally be written as
(TODAY()=A22)

--
Regards

Roger Govier


"vpschas" wrote in message
...
Hi Roger and daddylonglegs,

Thank you both for your help.

Yes you are correct in saying there is a formula calculation in G1
(=E1-F1)
and is set not to display zero's.

Sorry to be a nuisance, I feel incredibly stupid now as I still
can't
get
this to work.

Conditional formatting for cell A22
Condition 1=(TODAY()-A222)*(G22=0)*(A22<"") for RED
highlighting
Condition 2=(TODAY()-A22-1)*(G22=0)*(A22<"") for GREEN
highlighting

I've written it as above because A22 contains Saturday's date 2nd
Sept, so
as today is 4th I hoped the date would show in Red as there are
still
an
outstanding balance to pay in G22 but the date is not highlighted
with
any
color? Any ideas please?

Also I like to know exactly why things happen, so can you please
confirm
that I understand this correctly!

The first part of the formula is checking todays date and if A22 is
greater
than 2 or greater than -1, you've already said that G22=0 is to
check
if zero
balance in G22 but can you please explain the last part of the
formula
(A22<"") as I have no idea what this means?

Many thanks,
vpschas
(UK)
================================================== ==

"Roger Govier" wrote:

Hi

Compared with my earlier solution, Daddylonglegs has quite rightly
picked up that one also needs to test that there is a value in A1,
otherwise it will turn Red anyway.

However, the test (from what you now describe) should be against
column
G rather than F, so just replace this in Daddylonglegs formula.
=(TODAY()-A12)*(G1="")*(A1<"")
=(TODAY()-A1-1)*(G1="")*(A1<"")

However, it sounds as though there is a formula calculation in G1
(=E1-F1) and whilst you say it shows as blank if the amount has
been
paid in full, it may be showing 0 - but the format is set not to
display
zero's.

If this is the case, then you would probably have to change the
test
in
both case from G1="" to G1=0

--
Regards

Roger Govier


"vpschas" wrote in message
...
Not exactly,

Column A contains weekly dates.
Column E contains total amount due.
Column F contains Less Amount paid.
Column G shows balance carried forward to following week (if
balance
due in
Column E is paid in full in Column F, the cell in Column G is
blank,
if
still any amount owed, shows in currency).
Column E automatically updates to include any arrears from
previous
weeks
plus total amount due this week.

So what I'd like to be able to do is:

When Column A shows todays date, highlight in green, to indicate
that
it is
now due.

When todays date is 2 days past the due date in Column A ,
highlight
in red,
to indicate that it is now in arrears.

Then no highlighting when balance paid in full or the next
todays
date
is
reached as hopefully the green/red formatting will start again.

Hope this makes sense.

many thanks,
vpschas
(UK)


"daddylonglegs" wrote:

Can you confirm what you want to do?

If A1 contains today's date it should turn green if F1 is
blank?
Then
when
more than 2 day's have passed from today and F1 is still blank
show
red,
otherwise white?

If this isn't what you require then please give more detail -
e.g.
what
might F1 contain, a figure, some words a blank......

For the above scenario use for condition 1

=(TODAY()-A12)*(F1="")*(A1<"")

red format

and for condition 2

=(TODAY()-A1-1)*(F1="")*(A1<"")





"vpschas" wrote:

Hi,

Sorry, I couldn't get this to work.

Thanks
vpschas
(UK)

"Roger Govier" wrote:

Hi

Mark your range of data.
FormatConditional Formattinguse dropdown to select
Formula
Is
=AND(A1=TODAY(),ISBLANK(F1))
FormatPatternsGreen
Choose Add
Formula Is =AND(TODAY()-A12,ISBLANK(F1))
FormatPatternsRed

Change the second part of the AND statement to whatever
would
be
appropriate for what cell F would show is fully paid.

--
Regards

Roger Govier


"vpschas" wrote in
message
...
Hi,

I'd like to find out how to format an Excel 2002
worksheet
to
highlight the
current date (in column A) in green and then if it is
overdue
by more
than 2
days to change to red but if the amount has been paid in
full
(shown
in
column F) to revert back to white, please.

Many thanks,
vpschas
(UK)

Hi,

Definately heading in the right direction now.

Ok so now ALL the previous dates that had a brought forward balance ie money
still outstanding, are highlighted in red.

This would be perfect if I could get it to only show this on the current
week not all dates prior to todays date and any previous formatting in Column
A is returned to no color once the next due date is reached.

Many Thanks,
vpschas
(UK)




"Roger Govier" wrote:

Sorry
Of course it should be
(G22<0)
we are looking for the ones where there is an outstanding balance, not
the ones which are cleared.



--
Regards

Roger Govier


"vpschas" wrote in message
...
Have amended formatting as advised and copied and format painted the
conditional formatting into all cells in Column A but now only all the
previous dates where the balance outstanding in Column G is zero are
highlighted Red.

Thanks,
vpschas
(UK)

"Roger Govier" wrote:

Hi

The reason is, the 4th of Sept is only 2 days after the 2nd. The test
we
have set up, is for periods more than 2 days.
If you want to include 2 days, then the formula would need to be
amended
to either
(TODAY()-A22=2)
or
(TODAY()-A221)

The test for
(A22<"")
is ensuring that A22 is not blank or empty. As far as Excel is
concerned, in a date calculation, it treats the blank entry as
01/01/1900 hence the above tests would always return values greater
than
2 and empty cells would all be made Red by the conditional formula.

Incidentally the part of the formula that is set to
(TODAY()-A22-1)
could equally be written as
(TODAY()=A22)

--
Regards

Roger Govier


"vpschas" wrote in message
...
Hi Roger and daddylonglegs,

Thank you both for your help.

Yes you are correct in saying there is a formula calculation in G1
(=E1-F1)
and is set not to display zero's.

Sorry to be a nuisance, I feel incredibly stupid now as I still
can't
get
this to work.

Conditional formatting for cell A22
Condition 1=(TODAY()-A222)*(G22=0)*(A22<"") for RED
highlighting
Condition 2=(TODAY()-A22-1)*(G22=0)*(A22<"") for GREEN
highlighting

I've written it as above because A22 contains Saturday's date 2nd
Sept, so
as today is 4th I hoped the date would show in Red as there are
still
an
outstanding balance to pay in G22 but the date is not highlighted
with
any
color? Any ideas please?

Also I like to know exactly why things happen, so can you please
confirm
that I understand this correctly!

The first part of the formula is checking todays date and if A22 is
greater
than 2 or greater than -1, you've already said that G22=0 is to
check
if zero
balance in G22 but can you please explain the last part of the
formula
(A22<"") as I have no idea what this means?

Many thanks,
vpschas
(UK)
================================================== ==

"Roger Govier" wrote:

Hi

Compared with my earlier solution, Daddylonglegs has quite rightly
picked up that one also needs to test that there is a value in A1,
otherwise it will turn Red anyway.

However, the test (from what you now describe) should be against
column
G rather than F, so just replace this in Daddylonglegs formula.
=(TODAY()-A12)*(G1="")*(A1<"")
=(TODAY()-A1-1)*(G1="")*(A1<"")

However, it sounds as though there is a formula calculation in G1
(=E1-F1) and whilst you say it shows as blank if the amount has
been
paid in full, it may be showing 0 - but the format is set not to
display
zero's.

If this is the case, then you would probably have to change the
test
in
both case from G1="" to G1=0

--
Regards

Roger Govier


"vpschas" wrote in message
...
Not exactly,

Column A contains weekly dates.
Column E contains total amount due.
Column F contains Less Amount paid.
Column G shows balance carried forward to following week (if
balance
due in
Column E is paid in full in Column F, the cell in Column G is
blank,
if
still any amount owed, shows in currency).
Column E automatically updates to include any arrears from
previous
weeks
plus total amount due this week.

So what I'd like to be able to do is:

When Column A shows todays date, highlight in green, to indicate
that
it is
now due.

When todays date is 2 days past the due date in Column A ,
highlight
in red,
to indicate that it is now in arrears.

Then no highlighting when balance paid in full or the next
todays
date
is
reached as hopefully the green/red formatting will start again.

Hope this makes sense.

many thanks,
vpschas
(UK)


"daddylonglegs" wrote:

Can you confirm what you want to do?

If A1 contains today's date it should turn green if F1 is
blank?
Then
when
more than 2 day's have passed from today and F1 is still blank
show
red,
otherwise white?

If this isn't what you require then please give more detail -
e.g.
what
might F1 contain, a figure, some words a blank......

For the above scenario use for condition 1

=(TODAY()-A12)*(F1="")*(A1<"")

red format

and for condition 2

=(TODAY()-A1-1)*(F1="")*(A1<"")





"vpschas" wrote:

Hi,

Sorry, I couldn't get this to work.

Thanks
vpschas
(UK)

"Roger Govier" wrote:

Hi

Mark your range of data.
FormatConditional Formattinguse dropdown to select
Formula
Is
=AND(A1=TODAY(),ISBLANK(F1))
FormatPatternsGreen
Choose Add
Formula Is =AND(TODAY()-A12,ISBLANK(F1))
FormatPatternsRed

Change the second part of the AND statement to whatever
would
be
appropriate for what cell F would show is fully paid.

--
Regards

Roger Govier


"vpschas" wrote in
message
...
Hi,

I'd like to find out how to format an Excel 2002
worksheet
to
highlight the
current date (in column A) in green and then if it is
overdue
by more
than 2
days to change to red but if the amount has been paid in
full
(shown
in
column F) to revert back to white, please.

Many thanks,
vpschas
(UK)

Hi

Add another condition
*(TODAY()-A22)<=7)

Not sure whether you want that to be 7 or 9 (i.e. 7 days after the 2
days)

--
Regards

Roger Govier


"vpschas" wrote in message
...
Hi,

Definately heading in the right direction now.

Ok so now ALL the previous dates that had a brought forward balance ie
money
still outstanding, are highlighted in red.

This would be perfect if I could get it to only show this on the
current
week not all dates prior to todays date and any previous formatting in
Column
A is returned to no color once the next due date is reached.

Many Thanks,
vpschas
(UK)




"Roger Govier" wrote:

Sorry
Of course it should be
(G22<0)
we are looking for the ones where there is an outstanding balance,
not
the ones which are cleared.



--
Regards

Roger Govier


"vpschas" wrote in message
...
Have amended formatting as advised and copied and format painted
the
conditional formatting into all cells in Column A but now only all
the
previous dates where the balance outstanding in Column G is zero
are
highlighted Red.

Thanks,
vpschas
(UK)

"Roger Govier" wrote:

Hi

The reason is, the 4th of Sept is only 2 days after the 2nd. The
test
we
have set up, is for periods more than 2 days.
If you want to include 2 days, then the formula would need to be
amended
to either
(TODAY()-A22=2)
or
(TODAY()-A221)

The test for
(A22<"")
is ensuring that A22 is not blank or empty. As far as Excel is
concerned, in a date calculation, it treats the blank entry as
01/01/1900 hence the above tests would always return values
greater
than
2 and empty cells would all be made Red by the conditional
formula.

Incidentally the part of the formula that is set to
(TODAY()-A22-1)
could equally be written as
(TODAY()=A22)

--
Regards

Roger Govier


"vpschas" wrote in message
...
Hi Roger and daddylonglegs,

Thank you both for your help.

Yes you are correct in saying there is a formula calculation in
G1
(=E1-F1)
and is set not to display zero's.

Sorry to be a nuisance, I feel incredibly stupid now as I still
can't
get
this to work.

Conditional formatting for cell A22
Condition 1=(TODAY()-A222)*(G22=0)*(A22<"") for RED
highlighting
Condition 2=(TODAY()-A22-1)*(G22=0)*(A22<"") for GREEN
highlighting

I've written it as above because A22 contains Saturday's date
2nd
Sept, so
as today is 4th I hoped the date would show in Red as there are
still
an
outstanding balance to pay in G22 but the date is not
highlighted
with
any
color? Any ideas please?

Also I like to know exactly why things happen, so can you please
confirm
that I understand this correctly!

The first part of the formula is checking todays date and if A22
is
greater
than 2 or greater than -1, you've already said that G22=0 is to
check
if zero
balance in G22 but can you please explain the last part of the
formula
(A22<"") as I have no idea what this means?

Many thanks,
vpschas
(UK)
================================================== ==

"Roger Govier" wrote:

Hi

Compared with my earlier solution, Daddylonglegs has quite
rightly
picked up that one also needs to test that there is a value in
A1,
otherwise it will turn Red anyway.

However, the test (from what you now describe) should be
against
column
G rather than F, so just replace this in Daddylonglegs formula.
=(TODAY()-A12)*(G1="")*(A1<"")
=(TODAY()-A1-1)*(G1="")*(A1<"")

However, it sounds as though there is a formula calculation in
G1
(=E1-F1) and whilst you say it shows as blank if the amount has
been
paid in full, it may be showing 0 - but the format is set not
to
display
zero's.

If this is the case, then you would probably have to change the
test
in
both case from G1="" to G1=0

--
Regards

Roger Govier


"vpschas" wrote in message
...
Not exactly,

Column A contains weekly dates.
Column E contains total amount due.
Column F contains Less Amount paid.
Column G shows balance carried forward to following week (if
balance
due in
Column E is paid in full in Column F, the cell in Column G
is
blank,
if
still any amount owed, shows in currency).
Column E automatically updates to include any arrears from
previous
weeks
plus total amount due this week.

So what I'd like to be able to do is:

When Column A shows todays date, highlight in green, to
indicate
that
it is
now due.

When todays date is 2 days past the due date in Column A ,
highlight
in red,
to indicate that it is now in arrears.

Then no highlighting when balance paid in full or the next
todays
date
is
reached as hopefully the green/red formatting will start
again.

Hope this makes sense.

many thanks,
vpschas
(UK)


"daddylonglegs" wrote:

Can you confirm what you want to do?

If A1 contains today's date it should turn green if F1 is
blank?
Then
when
more than 2 day's have passed from today and F1 is still
blank
show
red,
otherwise white?

If this isn't what you require then please give more
detail -
e.g.
what
might F1 contain, a figure, some words a blank......

For the above scenario use for condition 1

=(TODAY()-A12)*(F1="")*(A1<"")

red format

and for condition 2

=(TODAY()-A1-1)*(F1="")*(A1<"")





"vpschas" wrote:

Hi,

Sorry, I couldn't get this to work.

Thanks
vpschas
(UK)

"Roger Govier" wrote:

Hi

Mark your range of data.
FormatConditional Formattinguse dropdown to select
Formula
Is
=AND(A1=TODAY(),ISBLANK(F1))
FormatPatternsGreen
Choose Add
Formula Is =AND(TODAY()-A12,ISBLANK(F1))
FormatPatternsRed

Change the second part of the AND statement to whatever
would
be
appropriate for what cell F would show is fully paid.

--
Regards

Roger Govier


"vpschas" wrote in
message
...
Hi,

I'd like to find out how to format an Excel 2002
worksheet
to
highlight the
current date (in column A) in green and then if it is
overdue
by more
than 2
days to change to red but if the amount has been paid
in
full
(shown
in
column F) to revert back to white, please.

Many thanks,
vpschas
(UK)

Sorry Roger,

Still not working.

I have added this as condition 3 but still no change, all previous dates
with a balance carried forward are still red.

Also I have noticed that when I've copied and format painted from A3 to
other cells in Column A the A3 cell reference in the formula stays the same.
I thought this should update automatically unless you include $'seither side
of it to make it an absolute ref?

Condition 3 in all cells in Column A now looks like this-
Formula is ="*(TODAY()-A3)<=7"

Any further ideas please?

Many thanks,
vpschas
(UK)

"Roger Govier" wrote:

Hi

Add another condition
*(TODAY()-A22)<=7)

Not sure whether you want that to be 7 or 9 (i.e. 7 days after the 2
days)

--
Regards

Roger Govier


"vpschas" wrote in message
...
Hi,

Definately heading in the right direction now.

Ok so now ALL the previous dates that had a brought forward balance ie
money
still outstanding, are highlighted in red.

This would be perfect if I could get it to only show this on the
current
week not all dates prior to todays date and any previous formatting in
Column
A is returned to no color once the next due date is reached.

Many Thanks,
vpschas
(UK)




"Roger Govier" wrote:

Sorry
Of course it should be
(G22<0)
we are looking for the ones where there is an outstanding balance,
not
the ones which are cleared.



--
Regards

Roger Govier


"vpschas" wrote in message
...
Have amended formatting as advised and copied and format painted
the
conditional formatting into all cells in Column A but now only all
the
previous dates where the balance outstanding in Column G is zero
are
highlighted Red.

Thanks,
vpschas
(UK)

"Roger Govier" wrote:

Hi

The reason is, the 4th of Sept is only 2 days after the 2nd. The
test
we
have set up, is for periods more than 2 days.
If you want to include 2 days, then the formula would need to be
amended
to either
(TODAY()-A22=2)
or
(TODAY()-A221)

The test for
(A22<"")
is ensuring that A22 is not blank or empty. As far as Excel is
concerned, in a date calculation, it treats the blank entry as
01/01/1900 hence the above tests would always return values
greater
than
2 and empty cells would all be made Red by the conditional
formula.

Incidentally the part of the formula that is set to
(TODAY()-A22-1)
could equally be written as
(TODAY()=A22)

--
Regards

Roger Govier


"vpschas" wrote in message
...
Hi Roger and daddylonglegs,

Thank you both for your help.

Yes you are correct in saying there is a formula calculation in
G1
(=E1-F1)
and is set not to display zero's.

Sorry to be a nuisance, I feel incredibly stupid now as I still
can't
get
this to work.

Conditional formatting for cell A22
Condition 1=(TODAY()-A222)*(G22=0)*(A22<"") for RED
highlighting
Condition 2=(TODAY()-A22-1)*(G22=0)*(A22<"") for GREEN
highlighting

I've written it as above because A22 contains Saturday's date
2nd
Sept, so
as today is 4th I hoped the date would show in Red as there are
still
an
outstanding balance to pay in G22 but the date is not
highlighted
with
any
color? Any ideas please?

Also I like to know exactly why things happen, so can you please
confirm
that I understand this correctly!

The first part of the formula is checking todays date and if A22
is
greater
than 2 or greater than -1, you've already said that G22=0 is to
check
if zero
balance in G22 but can you please explain the last part of the
formula
(A22<"") as I have no idea what this means?

Many thanks,
vpschas
(UK)
================================================== ==

"Roger Govier" wrote:

Hi

Compared with my earlier solution, Daddylonglegs has quite
rightly
picked up that one also needs to test that there is a value in
A1,
otherwise it will turn Red anyway.

However, the test (from what you now describe) should be
against
column
G rather than F, so just replace this in Daddylonglegs formula.
=(TODAY()-A12)*(G1="")*(A1<"")
=(TODAY()-A1-1)*(G1="")*(A1<"")

However, it sounds as though there is a formula calculation in
G1
(=E1-F1) and whilst you say it shows as blank if the amount has
been
paid in full, it may be showing 0 - but the format is set not
to
display
zero's.

If this is the case, then you would probably have to change the
test
in
both case from G1="" to G1=0

--
Regards

Roger Govier


"vpschas" wrote in message
...
Not exactly,

Column A contains weekly dates.
Column E contains total amount due.
Column F contains Less Amount paid.
Column G shows balance carried forward to following week (if
balance
due in
Column E is paid in full in Column F, the cell in Column G
is
blank,
if
still any amount owed, shows in currency).
Column E automatically updates to include any arrears from
previous
weeks
plus total amount due this week.

So what I'd like to be able to do is:

When Column A shows todays date, highlight in green, to
indicate
that
it is
now due.

When todays date is 2 days past the due date in Column A ,
highlight
in red,
to indicate that it is now in arrears.

Then no highlighting when balance paid in full or the next
todays
date
is
reached as hopefully the green/red formatting will start
again.

Hope this makes sense.

many thanks,
vpschas
(UK)


"daddylonglegs" wrote:

Can you confirm what you want to do?

If A1 contains today's date it should turn green if F1 is
blank?
Then
when
more than 2 day's have passed from today and F1 is still
blank
show
red,
otherwise white?

If this isn't what you require then please give more
detail -
e.g.
what
might F1 contain, a figure, some words a blank......

For the above scenario use for condition 1

=(TODAY()-A12)*(F1="")*(A1<"")

red format

and for condition 2

=(TODAY()-A1-1)*(F1="")*(A1<"")





"vpschas" wrote:

Hi,

Sorry, I couldn't get this to work.

Thanks
vpschas
(UK)

"Roger Govier" wrote:

Hi

Mark your range of data.
FormatConditional Formattinguse dropdown to select
Formula
Is
=AND(A1=TODAY(),ISBLANK(F1))
FormatPatternsGreen
Choose Add
Formula Is =AND(TODAY()-A12,ISBLANK(F1))
FormatPatternsRed

Change the second part of the AND statement to whatever

Hi

I'm sorry!!!
I didn't make myself clear, when I said add another condition. I meant
another test within the function, not another Condition in the
Conditional formatting

=(TODAY()-A1=2)*(G1="")*(A1<"")*(TODAY()-A1)<=7


--
Regards

Roger Govier


"vpschas" wrote in message
...
Sorry Roger,

Still not working.

I have added this as condition 3 but still no change, all previous
dates
with a balance carried forward are still red.

Also I have noticed that when I've copied and format painted from A3
to
other cells in Column A the A3 cell reference in the formula stays the
same.
I thought this should update automatically unless you include
$'seither side
of it to make it an absolute ref?

Condition 3 in all cells in Column A now looks like this-
Formula is ="*(TODAY()-A3)<=7"

Any further ideas please?

Many thanks,
vpschas
(UK)

"Roger Govier" wrote:

Hi

Add another condition
*(TODAY()-A22)<=7)

Not sure whether you want that to be 7 or 9 (i.e. 7 days after the 2
days)

--
Regards

Roger Govier


"vpschas" wrote in message
...
Hi,

Definately heading in the right direction now.

Ok so now ALL the previous dates that had a brought forward balance
ie
money
still outstanding, are highlighted in red.

This would be perfect if I could get it to only show this on the
current
week not all dates prior to todays date and any previous formatting
in
Column
A is returned to no color once the next due date is reached.

Many Thanks,
vpschas
(UK)




"Roger Govier" wrote:

Sorry
Of course it should be
(G22<0)
we are looking for the ones where there is an outstanding balance,
not
the ones which are cleared.



--
Regards

Roger Govier


"vpschas" wrote in message
...
Have amended formatting as advised and copied and format painted
the
conditional formatting into all cells in Column A but now only
all
the
previous dates where the balance outstanding in Column G is zero
are
highlighted Red.

Thanks,
vpschas
(UK)

"Roger Govier" wrote:

Hi

The reason is, the 4th of Sept is only 2 days after the 2nd.
The
test
we
have set up, is for periods more than 2 days.
If you want to include 2 days, then the formula would need to
be
amended
to either
(TODAY()-A22=2)
or
(TODAY()-A221)

The test for
(A22<"")
is ensuring that A22 is not blank or empty. As far as Excel is
concerned, in a date calculation, it treats the blank entry as
01/01/1900 hence the above tests would always return values
greater
than
2 and empty cells would all be made Red by the conditional
formula.

Incidentally the part of the formula that is set to
(TODAY()-A22-1)
could equally be written as
(TODAY()=A22)

--
Regards

Roger Govier


"vpschas" wrote in message
...
Hi Roger and daddylonglegs,

Thank you both for your help.

Yes you are correct in saying there is a formula calculation
in
G1
(=E1-F1)
and is set not to display zero's.

Sorry to be a nuisance, I feel incredibly stupid now as I
still
can't
get
this to work.

Conditional formatting for cell A22
Condition 1=(TODAY()-A222)*(G22=0)*(A22<"") for RED
highlighting
Condition 2=(TODAY()-A22-1)*(G22=0)*(A22<"") for GREEN
highlighting

I've written it as above because A22 contains Saturday's date
2nd
Sept, so
as today is 4th I hoped the date would show in Red as there
are
still
an
outstanding balance to pay in G22 but the date is not
highlighted
with
any
color? Any ideas please?

Also I like to know exactly why things happen, so can you
please
confirm
that I understand this correctly!

The first part of the formula is checking todays date and if
A22
is
greater
than 2 or greater than -1, you've already said that G22=0 is
to
check
if zero
balance in G22 but can you please explain the last part of
the
formula
(A22<"") as I have no idea what this means?

Many thanks,
vpschas
(UK)
================================================== ==

"Roger Govier" wrote:

Hi

Compared with my earlier solution, Daddylonglegs has quite
rightly
picked up that one also needs to test that there is a value
in
A1,
otherwise it will turn Red anyway.

However, the test (from what you now describe) should be
against
column
G rather than F, so just replace this in Daddylonglegs
formula.
=(TODAY()-A12)*(G1="")*(A1<"")
=(TODAY()-A1-1)*(G1="")*(A1<"")

However, it sounds as though there is a formula calculation
in
G1
(=E1-F1) and whilst you say it shows as blank if the amount
has
been
paid in full, it may be showing 0 - but the format is set
not
to
display
zero's.

If this is the case, then you would probably have to change
the
test
in
both case from G1="" to G1=0

--
Regards

Roger Govier


"vpschas" wrote in
message
...
Not exactly,

Column A contains weekly dates.
Column E contains total amount due.
Column F contains Less Amount paid.
Column G shows balance carried forward to following week
(if
balance
due in
Column E is paid in full in Column F, the cell in Column
G
is
blank,
if
still any amount owed, shows in currency).
Column E automatically updates to include any arrears from
previous
weeks
plus total amount due this week.

So what I'd like to be able to do is:

When Column A shows todays date, highlight in green, to
indicate
that
it is
now due.

When todays date is 2 days past the due date in Column A ,
highlight
in red,
to indicate that it is now in arrears.

Then no highlighting when balance paid in full or the next
todays
date
is
reached as hopefully the green/red formatting will start
again.

Hope this makes sense.

many thanks,
vpschas
(UK)


"daddylonglegs" wrote:

Can you confirm what you want to do?

If A1 contains today's date it should turn green if F1 is
blank?
Then
when
more than 2 day's have passed from today and F1 is still
blank
show
red,
otherwise white?

If this isn't what you require then please give more
detail -
e.g.
what
might F1 contain, a figure, some words a blank......

For the above scenario use for condition 1

=(TODAY()-A12)*(F1="")*(A1<"")

red format

and for condition 2

=(TODAY()-A1-1)*(F1="")*(A1<"")





"vpschas" wrote:

Hi,

Sorry, I couldn't get this to work.

Thanks
vpschas
(UK)

"Roger Govier" wrote:

Hi

Mark your range of data.
FormatConditional Formattinguse dropdown to select
Formula
Is
=AND(A1=TODAY(),ISBLANK(F1))
FormatPatternsGreen
Choose Add
Formula Is =AND(TODAY()-A12,ISBLANK(F1))
FormatPatternsRed

Change the second part of the AND statement to
whatever

Ok will try this, do I need to add the extra test to both Red and Green
conditions? Next due date is 9th.

Thank you for your help
vpschas
(uk)

"Roger Govier" wrote:

Hi

I'm sorry!!!
I didn't make myself clear, when I said add another condition. I meant
another test within the function, not another Condition in the
Conditional formatting

=(TODAY()-A1=2)*(G1="")*(A1<"")*(TODAY()-A1)<=7


--
Regards

Roger Govier


"vpschas" wrote in message
...
Sorry Roger,

Still not working.

I have added this as condition 3 but still no change, all previous
dates
with a balance carried forward are still red.

Also I have noticed that when I've copied and format painted from A3
to
other cells in Column A the A3 cell reference in the formula stays the
same.
I thought this should update automatically unless you include
$'seither side
of it to make it an absolute ref?

Condition 3 in all cells in Column A now looks like this-
Formula is ="*(TODAY()-A3)<=7"

Any further ideas please?

Many thanks,
vpschas
(UK)

"Roger Govier" wrote:

Hi

Add another condition
*(TODAY()-A22)<=7)

Not sure whether you want that to be 7 or 9 (i.e. 7 days after the 2
days)

--
Regards

Roger Govier


"vpschas" wrote in message
...
Hi,

Definately heading in the right direction now.

Ok so now ALL the previous dates that had a brought forward balance
ie
money
still outstanding, are highlighted in red.

This would be perfect if I could get it to only show this on the
current
week not all dates prior to todays date and any previous formatting
in
Column
A is returned to no color once the next due date is reached.

Many Thanks,
vpschas
(UK)




"Roger Govier" wrote:

Sorry
Of course it should be
(G22<0)
we are looking for the ones where there is an outstanding balance,
not
the ones which are cleared.



--
Regards

Roger Govier


"vpschas" wrote in message
...
Have amended formatting as advised and copied and format painted
the
conditional formatting into all cells in Column A but now only
all
the
previous dates where the balance outstanding in Column G is zero
are
highlighted Red.

Thanks,
vpschas
(UK)

"Roger Govier" wrote:

Hi

The reason is, the 4th of Sept is only 2 days after the 2nd.
The
test
we
have set up, is for periods more than 2 days.
If you want to include 2 days, then the formula would need to
be
amended
to either
(TODAY()-A22=2)
or
(TODAY()-A221)

The test for
(A22<"")
is ensuring that A22 is not blank or empty. As far as Excel is
concerned, in a date calculation, it treats the blank entry as
01/01/1900 hence the above tests would always return values
greater
than
2 and empty cells would all be made Red by the conditional
formula.

Incidentally the part of the formula that is set to
(TODAY()-A22-1)
could equally be written as
(TODAY()=A22)

--
Regards

Roger Govier


"vpschas" wrote in message
...
Hi Roger and daddylonglegs,

Thank you both for your help.

Yes you are correct in saying there is a formula calculation
in
G1
(=E1-F1)
and is set not to display zero's.

Sorry to be a nuisance, I feel incredibly stupid now as I
still
can't
get
this to work.

Conditional formatting for cell A22
Condition 1=(TODAY()-A222)*(G22=0)*(A22<"") for RED
highlighting
Condition 2=(TODAY()-A22-1)*(G22=0)*(A22<"") for GREEN
highlighting

I've written it as above because A22 contains Saturday's date
2nd
Sept, so
as today is 4th I hoped the date would show in Red as there
are
still
an
outstanding balance to pay in G22 but the date is not
highlighted
with
any
color? Any ideas please?

Also I like to know exactly why things happen, so can you
please
confirm
that I understand this correctly!

The first part of the formula is checking todays date and if
A22
is
greater
than 2 or greater than -1, you've already said that G22=0 is
to
check
if zero
balance in G22 but can you please explain the last part of
the
formula
(A22<"") as I have no idea what this means?

Many thanks,
vpschas
(UK)
================================================== ==

"Roger Govier" wrote:

Hi

Compared with my earlier solution, Daddylonglegs has quite
rightly
picked up that one also needs to test that there is a value
in
A1,
otherwise it will turn Red anyway.

However, the test (from what you now describe) should be
against
column
G rather than F, so just replace this in Daddylonglegs
formula.
=(TODAY()-A12)*(G1="")*(A1<"")
=(TODAY()-A1-1)*(G1="")*(A1<"")

However, it sounds as though there is a formula calculation
in
G1
(=E1-F1) and whilst you say it shows as blank if the amount
has
been
paid in full, it may be showing 0 - but the format is set
not
to
display
zero's.

If this is the case, then you would probably have to change
the
test
in
both case from G1="" to G1=0

--
Regards

Roger Govier


"vpschas" wrote in
message
...
Not exactly,

Column A contains weekly dates.
Column E contains total amount due.
Column F contains Less Amount paid.
Column G shows balance carried forward to following week
(if
balance
due in
Column E is paid in full in Column F, the cell in Column
G
is
blank,
if
still any amount owed, shows in currency).
Column E automatically updates to include any arrears from
previous
weeks
plus total amount due this week.

So what I'd like to be able to do is:

When Column A shows todays date, highlight in green, to
indicate
that
it is
now due.

When todays date is 2 days past the due date in Column A ,
highlight
in red,
to indicate that it is now in arrears.

Then no highlighting when balance paid in full or the next
todays
date
is
reached as hopefully the green/red formatting will start
again.

Hi

No, not to both, just the Red case.
The Green case is only triggered when cell A1=TODAY(), not on a range of
dates.

--
Regards

Roger Govier


"vpschas" wrote in message
...
Ok will try this, do I need to add the extra test to both Red and
Green
conditions? Next due date is 9th.

Thank you for your help
vpschas
(uk)

"Roger Govier" wrote:

Hi

I'm sorry!!!
I didn't make myself clear, when I said add another condition. I
meant
another test within the function, not another Condition in the
Conditional formatting

=(TODAY()-A1=2)*(G1="")*(A1<"")*(TODAY()-A1)<=7


--
Regards

Roger Govier


"vpschas" wrote in message
...
Sorry Roger,

Still not working.

I have added this as condition 3 but still no change, all previous
dates
with a balance carried forward are still red.

Also I have noticed that when I've copied and format painted from
A3
to
other cells in Column A the A3 cell reference in the formula stays
the
same.
I thought this should update automatically unless you include
$'seither side
of it to make it an absolute ref?

Condition 3 in all cells in Column A now looks like this-
Formula is ="*(TODAY()-A3)<=7"

Any further ideas please?

Many thanks,
vpschas
(UK)

"Roger Govier" wrote:

Hi

Add another condition
*(TODAY()-A22)<=7)

Not sure whether you want that to be 7 or 9 (i.e. 7 days after the
2
days)

--
Regards

Roger Govier


"vpschas" wrote in message
...
Hi,

Definately heading in the right direction now.

Ok so now ALL the previous dates that had a brought forward
balance
ie
money
still outstanding, are highlighted in red.

This would be perfect if I could get it to only show this on the
current
week not all dates prior to todays date and any previous
formatting
in
Column
A is returned to no color once the next due date is reached.

Many Thanks,
vpschas
(UK)




"Roger Govier" wrote:

Sorry
Of course it should be
(G22<0)
we are looking for the ones where there is an outstanding
balance,
not
the ones which are cleared.



--
Regards

Roger Govier


"vpschas" wrote in message
...
Have amended formatting as advised and copied and format
painted
the
conditional formatting into all cells in Column A but now
only
all
the
previous dates where the balance outstanding in Column G is
zero
are
highlighted Red.

Thanks,
vpschas
(UK)

"Roger Govier" wrote:

Hi

The reason is, the 4th of Sept is only 2 days after the 2nd.
The
test
we
have set up, is for periods more than 2 days.
If you want to include 2 days, then the formula would need
to
be
amended
to either
(TODAY()-A22=2)
or
(TODAY()-A221)

The test for
(A22<"")
is ensuring that A22 is not blank or empty. As far as Excel
is
concerned, in a date calculation, it treats the blank entry
as
01/01/1900 hence the above tests would always return values
greater
than
2 and empty cells would all be made Red by the conditional
formula.

Incidentally the part of the formula that is set to
(TODAY()-A22-1)
could equally be written as
(TODAY()=A22)

--
Regards

Roger Govier


"vpschas" wrote in
message
...
Hi Roger and daddylonglegs,

Thank you both for your help.

Yes you are correct in saying there is a formula
calculation
in
G1
(=E1-F1)
and is set not to display zero's.

Sorry to be a nuisance, I feel incredibly stupid now as I
still
can't
get
this to work.

Conditional formatting for cell A22
Condition 1=(TODAY()-A222)*(G22=0)*(A22<"") for RED
highlighting
Condition 2=(TODAY()-A22-1)*(G22=0)*(A22<"") for
GREEN
highlighting

I've written it as above because A22 contains Saturday's
date
2nd
Sept, so
as today is 4th I hoped the date would show in Red as
there
are
still
an
outstanding balance to pay in G22 but the date is not
highlighted
with
any
color? Any ideas please?

Also I like to know exactly why things happen, so can you
please
confirm
that I understand this correctly!

The first part of the formula is checking todays date and
if
A22
is
greater
than 2 or greater than -1, you've already said that G22=0
is
to
check
if zero
balance in G22 but can you please explain the last part of
the
formula
(A22<"") as I have no idea what this means?

Many thanks,
vpschas
(UK)
================================================== ==

"Roger Govier" wrote:

Hi

Compared with my earlier solution, Daddylonglegs has
quite
rightly
picked up that one also needs to test that there is a
value
in
A1,
otherwise it will turn Red anyway.

However, the test (from what you now describe) should be
against
column
G rather than F, so just replace this in Daddylonglegs
formula.
=(TODAY()-A12)*(G1="")*(A1<"")
=(TODAY()-A1-1)*(G1="")*(A1<"")

However, it sounds as though there is a formula
calculation
in
G1
(=E1-F1) and whilst you say it shows as blank if the
amount
has
been
paid in full, it may be showing 0 - but the format is set
not
to
display
zero's.

If this is the case, then you would probably have to
change
the
test
in
both case from G1="" to G1=0

--
Regards

Roger Govier


"vpschas" wrote in
message
...
Not exactly,

Column A contains weekly dates.
Column E contains total amount due.
Column F contains Less Amount paid.
Column G shows balance carried forward to following
week
(if
balance
due in
Column E is paid in full in Column F, the cell in
Column
G
is
blank,
if
still any amount owed, shows in currency).
Column E automatically updates to include any arrears
from
previous
weeks
plus total amount due this week.

So what I'd like to be able to do is:

When Column A shows todays date, highlight in green, to
indicate
that
it is
now due.

When todays date is 2 days past the due date in Column
A ,
highlight
in red,
to indicate that it is now in arrears.

Then no highlighting when balance paid in full or the
next
todays
date
is
reached as hopefully the green/red formatting will
start
again.

Hi again,

Ok, I have changed the conditional formula as follows.

You did previously say it should be (G22<0) not (G22="") as I am looking
for the ones with outstanding balance to change to Red.

Condition 1 =(TODAY()-A1=2)*(G1<0)*(A1<"")*(TODAY()-A1<=7) to change to
Red

Condition 2 =(TODAY()-A1-1)*(G1="")*(A1<"") to change to Green

So at the moment I have 2nd September highlighted in Red as there is still a
balance outstanding.

Roll on the 9th and I can see if it changes ok.
Fingers crossed, hopefully this will work now.

Many thanks for your patience,
Regards,
vpschas
(uk)

================================================== ==


"Roger Govier" wrote:

Hi

No, not to both, just the Red case.
The Green case is only triggered when cell A1=TODAY(), not on a range of
dates.

--
Regards

Roger Govier


"vpschas" wrote in message
...
Ok will try this, do I need to add the extra test to both Red and
Green
conditions? Next due date is 9th.

Thank you for your help
vpschas
(uk)

"Roger Govier" wrote:

Hi

I'm sorry!!!
I didn't make myself clear, when I said add another condition. I
meant
another test within the function, not another Condition in the
Conditional formatting

=(TODAY()-A1=2)*(G1="")*(A1<"")*(TODAY()-A1)<=7


--
Regards

Roger Govier


"vpschas" wrote in message
...
Sorry Roger,

Still not working.

I have added this as condition 3 but still no change, all previous
dates
with a balance carried forward are still red.

Also I have noticed that when I've copied and format painted from
A3
to
other cells in Column A the A3 cell reference in the formula stays
the
same.
I thought this should update automatically unless you include
$'seither side
of it to make it an absolute ref?

Condition 3 in all cells in Column A now looks like this-
Formula is ="*(TODAY()-A3)<=7"

Any further ideas please?

Many thanks,
vpschas
(UK)

"Roger Govier" wrote:

Hi

Add another condition
*(TODAY()-A22)<=7)

Not sure whether you want that to be 7 or 9 (i.e. 7 days after the
2
days)

--
Regards

Roger Govier


"vpschas" wrote in message
...
Hi,

Definately heading in the right direction now.

Ok so now ALL the previous dates that had a brought forward
balance
ie
money
still outstanding, are highlighted in red.

This would be perfect if I could get it to only show this on the
current
week not all dates prior to todays date and any previous
formatting
in
Column
A is returned to no color once the next due date is reached.

Many Thanks,
vpschas
(UK)




"Roger Govier" wrote:

Sorry
Of course it should be
(G22<0)
we are looking for the ones where there is an outstanding
balance,
not
the ones which are cleared.



--
Regards

Roger Govier


"vpschas" wrote in message
...
Have amended formatting as advised and copied and format
painted
the
conditional formatting into all cells in Column A but now
only
all
the
previous dates where the balance outstanding in Column G is
zero
are
highlighted Red.

Thanks,
vpschas
(UK)

"Roger Govier" wrote:

Hi

The reason is, the 4th of Sept is only 2 days after the 2nd.
The
test
we
have set up, is for periods more than 2 days.
If you want to include 2 days, then the formula would need
to
be
amended
to either
(TODAY()-A22=2)
or
(TODAY()-A221)

The test for
(A22<"")
is ensuring that A22 is not blank or empty. As far as Excel
is
concerned, in a date calculation, it treats the blank entry
as
01/01/1900 hence the above tests would always return values
greater
than
2 and empty cells would all be made Red by the conditional
formula.

Incidentally the part of the formula that is set to
(TODAY()-A22-1)
could equally be written as
(TODAY()=A22)

--
Regards

Roger Govier


"vpschas" wrote in
message
...
Hi Roger and daddylonglegs,

Thank you both for your help.

Yes you are correct in saying there is a formula
calculation
in
G1
(=E1-F1)
and is set not to display zero's.

Sorry to be a nuisance, I feel incredibly stupid now as I
still
can't
get
this to work.

Conditional formatting for cell A22
Condition 1=(TODAY()-A222)*(G22=0)*(A22<"") for RED
highlighting
Condition 2=(TODAY()-A22-1)*(G22=0)*(A22<"") for
GREEN
highlighting

I've written it as above because A22 contains Saturday's
date
2nd
Sept, so
as today is 4th I hoped the date would show in Red as
there
are
still
an
outstanding balance to pay in G22 but the date is not
highlighted
with
any
color? Any ideas please?

Also I like to know exactly why things happen, so can you
please
confirm
that I understand this correctly!

The first part of the formula is checking todays date and
if
A22
is
greater
than 2 or greater than -1, you've already said that G22=0
is
to
check
if zero
balance in G22 but can you please explain the last part of
the
formula
(A22<"") as I have no idea what this means?

Many thanks,
vpschas
(UK)
================================================== ==

"Roger Govier" wrote:

Hi

Compared with my earlier solution, Daddylonglegs has
quite
rightly
picked up that one also needs to test that there is a
value
in
A1,
otherwise it will turn Red anyway.

However, the test (from what you now describe) should be
against
column
G rather than F, so just replace this in Daddylonglegs
formula.
=(TODAY()-A12)*(G1="")*(A1<"")
=(TODAY()-A1-1)*(G1="")*(A1<"")

However, it sounds as though there is a formula
calculation
in
G1
(=E1-F1) and whilst you say it shows as blank if the
amount
has
been
paid in full, it may be showing 0 - but the format is set
not
to
display
zero's.

If this is the case, then you would probably have to
change
the
test
in
both case from G1="" to G1=0