0

I am simply looking to create a on-time vs. past due script. I have 3 columns (revised due date, original due date, and date closed). I want the following to take place. It works for the most part but I am getting false-positives on some data rows. Any help you can provide would be greatly appreciated!

  1. IF revised due date is empty, look at the original due date
  2. Compare the original due date to close date. If the close date is after original due date it should write "Past-Due" into the row-level column.

  3. Else if revised date is not empty...Compare the revised due date to close date. If the close date is after revised due date it should write "Past-Due" into the row-level column.

  4. Else it should write in On-Time.

IF
    (AND(OR

        (Internal_Audit_Issue__c.Issue_Resolution_Due_Date__c <= Internal_Audit_Issue__c.Date_Closed__c,

   Internal_Audit_Issue__c.Revised_Due_Date__c <= Internal_Audit_Issue__c.Date_Closed__c)),

    "Past-Due","On-Time")
  • I'm not sure how this line is going to evaluate when the revised due date is empty (a comparison between null/ blank value and a date): Internal_Audit_Issue__c.Revised_Due_Date__c <= Internal_Audit_Issue__c.Date_Closed__c. To be honest, I've not tested it and may be that's leading the entire OR condition evaluation incorrect. Also, why do you have an AND condition? it doesn't seem to do anything. – arut May 6 at 19:16
  • Could you try this? IF(ISBLANK(Internal_Audit_Issue__c.Revised_Due_Date__c), IF(OR(Internal_Audit_Issue__c.Issue_Resolution_Due_Date__c <= Internal_Audit_Issue__c.Date_Closed__c, Internal_Audit_Issue__c.Revised_Due_Date__c <= Internal_Audit_Issue__c.Date_Closed__c), "Past-Due", "On-Time"), IF(Internal_Audit_Issue__c.Issue_Resolution_Due_Date__c <= Internal_Audit_Issue__c.Date_Closed__c, "Past-Due", "On-Time") ). Assumption is that the only the revised date could be null and not the other 2 date fields. – arut May 6 at 19:17
  • Hello! Thank you for your quick response. I am a novice at best with scripting so thank you for you help. You are correct in your assumptions. Will give it a try! – Trey Perkins May 6 at 19:41
  • Hello! This worked perfectly. Thank you. Resolved! – Trey Perkins May 6 at 19:59
0

Maybe

IF (
    BLANKVALUE(
        Internal_Audit_Issue__c.Revised_Due_Date__c, 
        Internal_Audit_Issue__c.Issue_Resolution_Due_Date__c
    ) < BLANKVALUE(
        Internal_Audit_Issue__c.Date_Closed__c,
        TODAY()
    ),
    "Past-Due",
    "On-Time"
)

Note, I have taken the liberty to assume that if Date_Closed__c is empty, you could still close it today, so if the due_date is today and the closed date is empty, it shows 'On-Time'

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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