Need formula for part of my spreadsheet. Am tracking due dates with Conditional rules.

See the image below.Excel Functioning.PNG

I have used these rules to gain color highlighting functionality to track due dates

Rules:

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)

A =Due Date
B = Date Completed

I am now trying to add a column called "Completed on Time?" perhaps column C; with possible cell data as "Yes", "No", or empty if applicable (Job not yet due)

It would assist to have Purple and Red Columns assign "No" to the "Completed on Time?" [C] column
as well as to have Gray Columns assign "Yes" to Completed on Time [C] Column. With Orange and White Columns remaining blank.

If anyone is a true excel guru and could assist further, I hope to use this column to sum the "yes"'s and the "No's" For each month separately and to find the % Completed on time for each month.


I'll attach my current document if anyone can help!Excel Help.xlsx

THANKS!

Hi
Copy and paste this foruma into Cell C2 and copy down:

=IF(ISBLANK(B2),"",IF(B2<=A2,"YES",IF(B2>A2,"NO")))

Hope this helps.
Tony

Thanks Tony! This works perfectly!!! Before I close the thread, do you know of a way to adjust your given formula to have the white and orange columns say "Not yet Completed" rather than remaining blank?

If not, thanks a bunch!

Hi
Use this formula:

=IF(ISBLANK(B2),"Not Yet Completed",IF(B2<=A2,"YES",IF(B2>A2,"NO")))

Tony

Thank you so much Tony, that works great

Sorry to continue to ask for adjustments, I was not completely clear. is there any way to adjust that formula so that it appears as "not yet completed" if and only if there is a due date entered (DATA in Column [A]), while remaining blank if there is no due date entered (column [A] is blank)

Thanks!

Hi
Try this formula:

=IF(AND(A2<>"",ISBLANK(B2)),"Not Yet Completed",IF(B2<A2,"YES",IF(B2>A2,"NO","")))

Tony

Perfect, Thank you!

Thanks for the feedback.

Nevermind, I believe I solved it by modifying the equation to this.

=IF(AND(F2<>"",ISBLANK(G2)),"NOT YET COMPLETED",IF(G2<=F2,"YES",IF(G2>F2,"NO","")))

Actually Tony, By adding in the = sign to the equation, I believe it is now tracking Blank [G]s as being = to Blank [F]s. And listing those columns as "YES" for completed on time even when their is no job listed or no due date. Do you know how to adjust this equation so that Blank = Blank is Blank, But Due Date=Date Complete remains "YES" ?

=if(isnumber(g4),g4-e4,if(isnumber(f4),"incomplete"," "))

Above is the solution

Hey Tony, This is the equation I am using as described by your solution.

=IF(AND(F2<>"",ISBLANK(G2)),"NOT YET COMPLETED",IF(G2<F2,"YES",IF(G2>F2,"NO","")))


It works perfectly. However, I also need the column to state "YES" If the Due Date [F] is equal to the Date Completed [G]. Currently it leaves them blank! How would I adjust this formula?

Thanks!

I found the problem's solution as listed above, Thanks for the help!