Structure Formula showing OVERDUE tickets (Duedate<Resolved)

lau pel

HI

I am stuck in a Formula definition.

My goal is to mark with "OVERDUE" tickets by comparing duedate and resolved date. When applying this formula...the tickets where duedate and resolved date are the same (same day) come out as OVERDUE...which I don't want. Can you help me?

 

/* Displays overdue tickets by comparing duedate and resolved date.
*/

 IF (statuscategory != "done" and resolved!=0 AND duedate < today()):
"OVERDUE"
ELSE if (statuscategory="done" AND duedate<(resolved) and duedate != resolved AND RESOLVED != 0):
"OVERDUE"

Regards

Laura

Stepan Kholodov

Hello @lau pel 

The formula returns OVERDUE when an issue is resolved on the same day as Due date because the actual values in these fields are different. The default Due date field is the Date type field with no time parameter and Resolved is the Date/Time type of field. The Formula operates with dates as their timestamps: Resolved gets its timestamp based on the set date and time, and Due date gets its timestamp from the date and midnight time. 

Removing the time parameter(and setting it to midnight as well) should help:

/* Displays overdue tickets by comparing duedate and resolved date.
*/

 IF (statuscategory != "done" and resolved!=0 AND duedate < today()):
"OVERDUE"
ELSE if (statuscategory="done" AND duedate<truncate_time(resolved) and duedate != truncate_time(resolved) AND RESOLVED != 0):
"OVERDUE"

Best regards,
Stepan Kholodov
Tempo