I need help with this formula. I have tested the date dif section and I believe that part (the majority of this) is sound. I think my problem stems from there sometimes being empty values in some of the fields. I have also been known to misplace brackets.
Fields I reference:
a) Received Target Date (text field containing date)
b) Received Actual Date (text field containing date)
c) Review Completion Actual Date (text field containing date)
d) Received Actual Plus10 (calculation yielding date +10 workdays from B
=IF(OR([Received Target Date]="ON HOLD",[Received Target Date]="",[Received Target Date]="NA"),"NA",(IF(OR((DATEDIF([Received Actual Date],[Review Completion Actual Date],"d")-(((DATEDIF([Received Actual Date],[Review Completion Actual Date],"d")+WEEKDAY([Received Actual Date],3)-WEEKDAY([Review Completion Actual Date],3))/7)2)+1(WEEKDAY([Received Actual Date],3)>5)-(WEEKDAY([Review Completion Actual Date],3)-4)*(WEEKDAY([Review Completion Actual Date],3)>4))<10,AND([Review Completion Actual Date]="",TODAY()>[Received Actual Plus10])),"Behind","On Target")))
Problems:
1) If A is filled, even if it's one year in the future, it still says I'm "behind".
2) If both A and B are filled out, I get #NUM!
As a reference, here is the formula I use for D
IF(ISBLANK([Received Actual Date]),"",[Received Actual Date]+14)
If someone wants I can share the Excel formula i based this on that currently works.
