Forum Discussion

AngelaBowman's avatar
AngelaBowman
Qrew Cadet
8 years ago

Formula help

I'm trying to create a formula that states a task is overdue.  I have it working properly for one statement, but can't get it right if I add another condition.  I need it to state a filing is overdue if the day is past today, unless the filing is on extension, then I need it to use the extension due date to calculate.  I have a date return filed, a due date, extension due date and a checkbox if the return is on extension.  Below is I'm original formula, but it doesn't work if the return is on extension.


If(IsNull([Date Return Filed]) and ((Today() >[Due Date]) or (Today()> [Extension Due])),  "<span style=\"color:#ff0707; font-weight:bold\">"&"OVERDUE"&"</span>" )


Thoughts?  Thank you.

  • ChrisChris's avatar
    ChrisChris
    Qrew Assistant Captain
    8 years ago
    If it's possible for Extension Due to be null, then you may want to check for that when comparing Today() to [Due Date]. If (Today() > [Due Date] AND isnull([Extension Due])) OR if not isnull([Extension Date] ... ...) This is pseudo syntax, but I think you get the gist of it.
  • AngelaBowman's avatar
    AngelaBowman
    Qrew Cadet
    8 years ago

    I don't quite get is gist as I am fairly new to this....

    I got this far, but coming up with a Type Mismatch...  Thoughts?

    If(IsNull([Extension Due]) and (Today() >[Due Date]), "<span style=\"color:#ff0707; font-weight:bold\">"&"OVERDUE"&"</span>" ) OR If(Not IsNull([Extension Due]) and (Today() >[Extension Due]), "<span style=\"color:#ff0707; font-weight:bold\">"&"OVERDUE"&"</span>")

  • ChrisChris's avatar
    ChrisChris
    Qrew Assistant Captain
    8 years ago
    Okay, so we'll go back to the start. Your original code allows for both expressions in the OR statement to be true at the same time. Assuming that [extension due] can be null, we can fine tune the syntax and we create the following...

    If(

    IsNull([Date Return Filed]) and

    (isnull([Extension Due]) AND Today() >[Due Date]) or (not isnull([Extension Due]) AND Today()> [Extension Due]), 

    "<span style=\"color:#ff0707; font-weight:bold\">"&"OVERDUE"&"</span>"," Else text displayed here"

    // end if
    )

    I'm assuming some things here -- that [Due Date] cannot be null and that [Extension Due] can be null. I'll need your help understanding your requirements in that area better.
  • AngelaBowman's avatar
    AngelaBowman
    Qrew Cadet
    8 years ago
    This works perfect! ... all of your assumptions are correct... thank you!