I'm using a simple formula to calculate the due date difference between today and the project due date:
WORKDAY_DIFF(TODAY(), {Due Date})-1
Which works for anything due today and in the future, but does not calculate properly for past due things. If today is 11/19, something due 11/18 should be Past Due by -1, but it shows -3. Given that it works for current/future dates, what am I missing here for the past dates?
 
1 Solution
Accepted Solutions
WORKDAY_DIFF outputs "1" for the difference between the same day, so one day ago outputs "-2", your formula then does a "-1", so it's doing "-2-1", resulting in "-3"
Try using an IF to handle it, and here's an example where "TODAY()" is harcoded as a field:
IF(
WORKDAY_DIFF(
Date,
{TODAY()}
)>0,
WORKDAY_DIFF(
Date,
{TODAY()}
) - 1,
WORKDAY_DIFF(
Date,
{TODAY()}
) + 1
)
WORKDAY_DIFF outputs "1" for the difference between the same day, so one day ago outputs "-2", your formula then does a "-1", so it's doing "-2-1", resulting in "-3"
Try using an IF to handle it, and here's an example where "TODAY()" is harcoded as a field:
IF(
WORKDAY_DIFF(
Date,
{TODAY()}
)>0,
WORKDAY_DIFF(
Date,
{TODAY()}
) - 1,
WORKDAY_DIFF(
Date,
{TODAY()}
) + 1
)
