Task overdue help

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

I need help with my current formula.  Right now, it returns "OVERDUE" if a report is not presented in time.  The issue is that I need the Overdue to not populate if a date is in the "Date report presented" field, even if it is past the due date or extension date.  Below is my current formula:

Thanks!


If(

[Will a report be prepared?]="yes" and

(IsNull([Date report presented])) and

(IsNull([Report Extension Due]) and Today() >[Report Due Date]) or (not IsNull([Report Extension Due]) and Today()> [report Extension Due]), 

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

// end if

)

Photo of Angela

Angela

  • 270 Points 250 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Lisa

Lisa

  • 260 Points 250 badge 2x thumb
I made these fields 
Date fields :Report Due Date, Date report presented, Report Extension Due
Text, Multiple Choice field:Will a report be prepared? ( options Yes, No)
Formula Text field: warning

If(
[Will a report be prepared?]="Yes" and
(IsNull([Date report presented])) and
(IsNull([Report Extension Due]) and Today() >[Report Due Date]) or (not IsNull([Report Extension Due]) and Today()> [Report Extension Due]), 
"OVERDUE"," " )

The only change made to your warning field is "Yes" vs. "yes" and added the colorization as an option to a report: If([warning]="OVERDUE","pink")
Photo of Angela

Angela

  • 270 Points 250 badge 2x thumb

thank you, but I still cant get it to work 100% properly.  If the report was presented late or on the due date , it still shows overdue.  If you put a date of 11/7 in the first, does yours still show overdue?

Photo of Lisa

Lisa

  • 260 Points 250 badge 2x thumb
Sorry - I didn't consider 'Today'. I made an addition to your original formula with the last line:

If(

[Will a report be prepared?]="Yes" and

(IsNull([Date report presented])) and (IsNull([Report Extension Due]) and Today() >[Report Due Date]) or

(not IsNull([Report Extension Due]) and Today()> [Report Extension Due]) or

[Date report presented] > Today()  and [Date report presented] > [Report Due Date],
 
"OVERDUE"," " )
Photo of Angela

Angela

  • 270 Points 250 badge 2x thumb
Thanks Lisa...  I guess I might have to rethink this as it's still not working for me:(
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb

Try this


IF(

[Will a report be prepared?]<>"Yes", "",  // no reported needed then null

not isnull([Date report presented]),''",  // report completed, so null

// ok so if we get this far in the IF then we know we need a report 

// and it's not yet done

// but are we overdue yet?

Today()< [Report Extension Due],

"<span style=\"color:#ff0707; font-weight:bold\">"&"OVERDUE"&"</span>")

Photo of Angela

Angela

  • 270 Points 250 badge 2x thumb
Thank you!  I had to make an adjustment and add back in my or statement to account for those not on extension, but finally got there.