Tweak Existing "DAYS LATE" formula to look at 2nd column for completion

[Lisa Nicoletta]

Objective: to show days late, based on a projected date vs today's date UNLESS a completed date is showing in a different column

Current columns:
M4: has TODAY() formula to display today's date
M10: no formula. Projected Meeting Date is entered here
N10: no formula. Actual Meeting Date is entered here
P10: current formula only shows days late based on M10 and M4.... =IF(COUNT(M10,$M$4)=2,TEXT($M$4-M10,"0 ""Day(s) Late"";0 "" Day(s) Early"";""On Time"""),"")

What I'm looking for is the correct formula that would also look at cell N10 to see if there is a date (person had meeting). If there IS a date in cell N10, then P10 would display COMPLETE.

Please help Pretty please!

 

[RCBricker]

will this work?

=if(P10="","","Complete")

rich

 

[CodeNinja]

=IF(ISNUMBER(N10),"Complete",IF(COUNT(M10,$M$4)=2,TEXT($M$4-M10,"0 ""Day(s) Late"";0 "" Day(s) Early"";""On Time"""),""))

 

[RCBricker]

=IF(ISNUMBER(N10),"Complete",IF(COUNT(M10,$M$4)=2,TEXT($M$4-M10,"0 ""Day(s) Late"";0 "" Day(s) Early"";""On Time"""),""))

great formula ninja. I was just going to change my formula as I realized the OP asked for one to check both.

However would the criteria for "Complete" not be better served to check if it is a date in the cell rather than a number?

something like

=IF(LEFT(CELL("format",N10),1)="D","Complete",IF(COUNT(M10,$M$4)=2,TEXT($M$4-M10,"0 ""Day(s) Late"";0 "" Day(s) Early"";""On Time"""),""))

Rich

 

[Lisa Nicoletta]

will this work?

=if(P10="","","Complete")

rich

Not sure if I didn't add it into the existing formula correctly or not...but it threw an error. BUT....I believe I figured out a different way.
Thank you for your quick response!

 

[Lisa Nicoletta]

=IF(ISNUMBER(N10),"Complete",IF(COUNT(M10,$M$4)=2,TEXT($M$4-M10,"0 ""Day(s) Late"";0 "" Day(s) Early"";""On Time"""),""))

Thank you (once again) CodeNinja! I actually used a formula that you previously gave me for a similar, but different scenario and it worked!
=IF(ISBLANK(N9),IF(COUNT(M9,$M$4)=2,TEXT($M$4-M9,"0 ""Day(s) Late"";0 "" Day(s) Early"";""On Time"""),""),"COMPLETE")