Formula help

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered

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.

Photo of Angela

Angela

  • 270 Points 250 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb
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.
Photo of Angela

Angela

  • 270 Points 250 badge 2x thumb

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

Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb
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.
Photo of Angela

Angela

  • 270 Points 250 badge 2x thumb
This works perfect! ... all of your assumptions are correct... thank you!