Discussions

Expand all | Collapse all

Task overdue help

  • 1.  Task overdue help

    Posted 11-20-2017 20:51

    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

    )



  • 2.  RE: Task overdue help

    Posted 11-21-2017 21:22
    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")


  • 3.  RE: Task overdue help

    Posted 11-22-2017 13:57

    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?



  • 4.  RE: Task overdue help

    Posted 11-22-2017 16:15
    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"," " )


  • 5.  RE: Task overdue help

    Posted 11-24-2017 16:04
    Thanks Lisa...  I guess I might have to rethink this as it's still not working for me:(


  • 6.  RE: Task overdue help

    Posted 11-24-2017 17:27

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



  • 7.  RE: Task overdue help

    Posted 11-24-2017 18:19
    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.