#1
|
|||
|
|||
Need help with conditional formatting
Hi,
I need help with conditional formatting, I have tried Highlight rules but am unable to get it to work. I am trying to 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. 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. Sorry, my English is not so good....so if there is anything missing/unexplained please message to me. I have attached herewith a sample test file, hope it helps. Thank you. |
#2
|
||||
|
||||
Select from A4 to D7 and try these conditional formatting formulas:
=AND($F4<>"Paid",TODAY()-DATE(YEAR(TODAY()),MONTH(TODAY()),$E4)>=6) for orange =AND($F4<>"Paid",TODAY()-DATE(YEAR(TODAY()),MONTH(TODAY()),$E4)>=3) for blue =AND($F4<>"Paid",TODAY()-DATE(YEAR(TODAY()),MONTH(TODAY()),$E4)>0) for green in that order |
#3
|
|||
|
|||
Thanks a lot.
I will try it out and revert back if any issues. Best regards |
#4
|
||||
|
||||
Please also let us know if it works.
|
#5
|
|||
|
|||
Sure, will do.
Thanks. |
#6
|
|||
|
|||
Actually, I am working on an another project and I am stuck at copying the formula, may be you can help me with that.
I have a total of 13 sheets ...12 of them renamed to Jan, Feb, Mar, Apr...upto Dec, Sheet13 renamed to Accounts. In each sheet Jan to Dec ...on daily basis data is entered. Everything is fine. In the Account sheet data is taken from each sheet (Jan to Dec)... for example (in Account sheet) =IF(Jan!$A1="","",(Jan!$A$2)) =IF(Jan!$A3="","",(Jan!$A$4)) =IF(Jan!$A5="","",(Jan!$A$6)) Its easy to copy paste the formula for all 31 days in Jan, but when it comes to Feb, how do I copy paste the formula? for example =IF(Feb!$A1="","",(Feb!$A$2)) =IF(Feb!$A1="","",(Feb!$A$2)) =IF(Feb!$A3="","",(Feb!$A$4)) rename Jan to Feb, then Jan to Mar, Jan to Apr...and so on... Please help. Thanks. |
#7
|
||||
|
||||
Your last post is a duplicate post for https://www.msofficeforums.com/excel...t1-sheet2.html
Please do not ask the same question in different threads. You can always "bump" a thread if you do not receive helpful answers. This thread is now closed
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Conditional formatting | bbutl027 | Excel | 15 | 12-05-2016 08:56 PM |
Conditional Formatting | sdfrance | Excel | 3 | 01-06-2015 07:10 AM |
Conditional formatting that ignores other formatting rules | info_guy2 | Excel | 1 | 07-03-2014 10:07 AM |
Conditional Formatting | jdc15371 | Excel | 2 | 06-26-2014 06:34 AM |
Conditional Formatting | teza2k06 | Excel | 3 | 01-19-2014 02:34 AM |