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")))


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.

1 Answer 1


TODAY() will (most likely) not do what you want it to do

How to use Today and Me in Calculated column

  • Right, I thought that may be the issue. Do you have any suggestions on creative ways to achieve similar functionality? Basically I have a number of formulas that the user wants to check for On Time or Behind, and they want the formula to recognize that Today is past a certain date, it is considered behind.
    – dave
    Commented Apr 11, 2016 at 17:35
  • No, the link I provided has all the goodies I know of. Commented Apr 11, 2016 at 18:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.