Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Closed Thread
 
LinkBack Thread Tools Display Modes
  #1  
Old 09-07-2017, 07:30 PM
mikehk mikehk is offline Windows 10 Office 2013
Novice
 
Join Date: Sep 2017
Posts: 15
mikehk is on a distinguished road
Default 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.
Attached Files
File Type: xlsx mn_alerts.xlsx (9.6 KB, 11 views)
  #2  
Old 09-11-2017, 05:10 AM
NBVC's Avatar
NBVC NBVC is offline Windows 10 Office 2013
A Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 103
NBVC is on a distinguished road
Default

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  
Old 09-17-2017, 08:33 PM
mikehk mikehk is offline Windows 10 Office 2013
Novice
 
Join Date: Sep 2017
Posts: 15
mikehk is on a distinguished road
Default

Thanks a lot.
I will try it out and revert back if any issues.

Best regards
  #4  
Old 09-22-2017, 05:30 AM
NBVC's Avatar
NBVC NBVC is offline Windows 10 Office 2013
A Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 103
NBVC is on a distinguished road
Default

Please also let us know if it works.
  #5  
Old 09-22-2017, 06:11 PM
mikehk mikehk is offline Windows 10 Office 2013
Novice
 
Join Date: Sep 2017
Posts: 15
mikehk is on a distinguished road
Default

Sure, will do.

Thanks.
  #6  
Old 09-22-2017, 06:21 PM
mikehk mikehk is offline Windows 10 Office 2013
Novice
 
Join Date: Sep 2017
Posts: 15
mikehk is on a distinguished road
Default

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  
Old 09-22-2017, 11:20 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 1,901
Pecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nice
Default

Your last post is a duplicate post for http://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
Closed Thread
Please reply to this thread with any new information or opinions.

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 04:36 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft