Calculating work date differences for due dates

[Emily_McLain]

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?

 

 

[TheTimeSavingCo · Accepted solution]

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
)