Discussions

Expand all | Collapse all

Formula help

  • 1.  Formula help

    Posted 09-18-2017 15:00

    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.



  • 2.  RE: Formula help

    Posted 09-18-2017 15:21
    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.


  • 3.  RE: Formula help

    Posted 09-18-2017 20:18

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



  • 4.  RE: Formula help

    Posted 09-18-2017 20:32
    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.


  • 5.  RE: Formula help

    Posted 09-18-2017 20:59
    This works perfect! ... all of your assumptions are correct... thank you!