Conditional format to highlight cells every month on a fixed date

[mikehk]

I wish to highlight cells on specific dates on monthly basis. I have
tried many ways but am unable to get it work, requesting for help.
Highly appreciate.

example :

# | Name | From | To | Due every

Contract 1 | Abc Co | 15 May 2016 | 14 May 2017 | 15
Contract 2 | Xyz Co | 1 Jan 2015 | 31 Dec 2018 | 1
and so on....

I would like to highlight data on "due every" number,
change the highlight color after 3 days, and another color
after 6 days (starting from the first due date).

I hope I have described my question clearly, if not,
please let me know.

Thank you.

 

[ryan0521]

1. Select cells A2 through A7. Do this by dragging from A2 to A7.
2. Then, click Home > Conditional Formatting > New Rule.
3. In the New Formatting Rule dialog box, click Use a formula to determine which cells to format.
4. Under Format values where this formula is true, type the formula: =XXXXXXX
5. Click Format.
6. In the Color box, select Red. In the Font Style box, select Bold.
7. Click OK until the dialog boxes are closed.
   The formatting is applied to column A.

 

[mikehk]

Hi Ryan,

Thanks for taking time to reply. Unfortunately I am stuck at
the formula, can you please help me with the formula.

Thank you.

1. Select cells A2 through A7. Do this by dragging from A2 to A7.
2. Then, click Home > Conditional Formatting > New Rule.
3. In the New Formatting Rule dialog box, click Use a formula to determine which cells to format.
4. Under Format values where this formula is true, type the formula: =XXXXXXX
5. Click Format.
6. In the Color box, select Red. In the Font Style box, select Bold.
7. Click OK until the dialog boxes are closed.
   The formatting is applied to column A.

 

[ryan0521]

Please be specific on "due every" column, do you put the values manually?

 

[mikehk]

That's correct. I put the values manually.
Sorry, my English is not so good....but I will try to explain as best as I can.

What I am trying to do is get an indication on which payment to chase.
for example if the contract is for 1 year starting 15/May/2017 to 14/May/2018,
payments fall due on every month starting 15th so if this value of start date
can be input automatically it will be nice, in fact this column may not be required
if the cells are highlighted on the contract start date of every month.

Once the cells are highlighted in say green color, I would want the color to change
to yellow after 3 days indicating the payment is past due, then the color to change
to red after 6 days indicating the payment is over due.

I think I may have to add a column for "paid", once it's paid there is no
need to highlight cells.

Please see image.

Thank you.

[/URL][/IMG]

Please be specific on "due every" column, do you put the values manually?

 

[ryan0521]

How did you get 3 days, 3+3 days and 3+6 days?