Template for tracking project completion dates and simple metrics? % Completed on time

I am trying to make a simple excel template on 2013 to track future project completion dates under the following paramaters, I also want a color to coordinate with each due date status for that corresponding row:

1. If they are completed by due date; Gray
2. If they are due by due date within 7 days of the current date; Orange
3. If they are over their due date and not yet completed; Red
4. If they are then completed, however past deadline; Purple


Lastly I would like to track the percentage of projects in this template that do not meet the due date for each month. (% of Red and Purple Columns).


I have these columns..
A: Order #, B: Due Date, C: Date Completed, D: Completed on time? And hope that column D will automatically be generated based on a formula specifying whether or not Column D row's data is > than that column C's row data for each row or =/< than Column B Data.


To satisfy my last requirement I then obviously will need columns or a chart to show the percentage each month that meets deadlines versus the percent that does not.


I assume I will use conditional formatting. If you can specify equations that can be placed as formula rules for conditional formatting as well as for being entered within individual columns it would help a ton. Thanks! I'd give my equations but they are a mess....

This was my starting point... http://chandoo.org/wp/2012/05/22/hig...e-dates-excel/

Please advise!

JBeaucaire,

Thank you for taking the time to help. Attached below on the left side is what I have gotten from the equations you listed. The dates seem to be functioning correctly. However, I would like the rows that the dates are listed within the be the ones highlighted.


Below on the right is an example of roughly what I need. Also as shown I will need an equation to track monthly metrics of meeting deadlines based on a "yes" or "no" and also an equation to generate a "yes" or "no" based on the date completed vs the due date.

Excel Deadlines.PNG

or

\1
or
http://postimg.org/image/egc3vgmfh/

I changed the gray to green on the right side

JBeaucaire,

Thanks again for the help. I'm having difficulty still. These last functions do not seem to function 100% correctly and the rows above the entered data seem to be the ones affected by color. The formulas you first gave me gave 100% date functionality, however they were highlighting the rows above the data. The code you recently gave me is not appearing to give correct functionality.

Lets say A is Job#, B is Due Date, C is Date completed, and D is Completed on time?: Yes/No.

To remind you, it should be like this.
Red=Overdue due date (Entered Due date [B] is past TODAY, and Date Completed [C] is empty)
Orange= Due Date [B] is within 7 days of TODAY, Date Completed [C] is empty)
Purple=Job has been completed, but was finished after the due date. (Date completed [C] is > than Date Due [B]
Gray=Job completed on or before due date. Date completed [C] is = to or < than Date Due [B]
Jobs and their rows not yet completed, but also not yet due within the next 7 days will remain white.


Lastly it would assist to have Purple and Red Columns assign "No" to Completed on Time [D] column
as well as to have Gray Columns assign "Yes" to Completed on Time [D] Column.

Thanks!

JBeaucaire,

I played around with the formulas, it seems I was entering yours incorrectly.

I have correctly highlighted each date using the following.

Red: =AND(COUNT($A1,$B1)=1, $A1<TODAY())
Orange: =AND(COUNT($A1,$B1)=1, $A1<=TODAY()+6)
Purple: =AND(COUNT($A1,$B1)=2, $A1<$B1)
Gray: =AND(COUNT($A1,$B1)=2,$A1>=$B1)

WITH A being DUE DATE and B being DATE COMPLETED

Thank you so much for your assistance, now I will seek out how to assign a "yes" or "no" value to a "completed on time" Row