Apply green to current date, red to 2 days overdue and none if pai

[Guest]

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)

 

[Roger Govier]

Hi

Mark your range of data.
Format>Conditional Formatting>use dropdown to select Formula Is
=AND(A1=TODAY(),ISBLANK(F1))
Format>Patterns>Green
Choose Add
Formula Is =AND(TODAY()-A1>2,ISBLANK(F1))
Format>Patterns>Red

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

 

[Guest]

Hi,

Sorry, I couldn't get this to work.

Thanks
vpschas
(UK)

 

[Guest]

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()-A1>2)*(F1="")*(A1<>"")

red format

and for condition 2

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

 

[Guest]

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)

 

[Roger Govier]

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()-A1>2)*(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

 

[Guest]

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()-A22>2)*(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]

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()-A22>1)

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)

 

[Guest]

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]

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.

 

[Guest]

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]

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)

 

[Guest]

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]

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

 

[Guest]

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)

 

[Guest]

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]

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.

 

[Roger Govier]

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.

 

[Guest]

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)

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

 

[Guest]

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)

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