Because you already have the days calculated in {Days}, you don’t need the same thing again in your others. You can also combine the check against your {Status} field in the same formula, so that you’ll just have {Days} and {Time left}, and that’s it.

What’s not correct about it? Based on your screenshot, it looks fine. The value is negative because that due date is in the past. If you want to make it positive instead of negative, wrap the ABS() function around it.

I combined all of these things into the following formula. I also left the number off the front if it’s due today, as the 0 isn’t adding any useful information to the “Due today” message. Let me know if it works for your needs:

IF(Status = "Complete", "", IF(Days = 0, "Due today ", ABS(Days) & IF(Days > 0, " days left ", " days overdue ")))

Thanks for all the help with this. Why would it say overdue by 3 days when it’s really only overdue by 1 day? This is the main issue I’m referring to.

This should illustrate the problem:

I don’t understand why WORKDAY_DIFF() doesn’t count a difference against TODAY() or NOW() as 0, and uses 1 instead. On top of that, I don’t know why it jumps from 1 to -2.

In my mind, I thought, “Using DATETIME_DIFF never causes problems like this!” But then I tested it, and was proven wrong. Notice the two records with a difference of 0, even though they’re different days:

“Okay,” I thought. “I just need to add my local time and everything will correct itself.”

Nope. It just moves the problem by one day.

To quote Pepe Le Pew, “Le sigh.”

Anyway, your problem is coming because you’re subtracting 1 from the WORKDAY_DIFF() total so that you can test against zero for the “Due today” option. Subtracting 1 from a negative number (for overdue tasks) means it goes farther away from zero, effectively adding a day to the calculation. That, on top of the already-shifted negative value offset that starts counting at -2, is why the number feels like it’s two days off (see the far right column, where it goes 2, 1, 0, -3, -4, etc.).

I suggest leaving your {Days} calculation alone, and correcting the issue in the other formula:

IF(Status = "Complete", "", IF(Days = 0, "Due today ", (ABS(Days) - (2*(Days < 0))) & IF(Days > 0, " days left ", " days overdue ")))

Even though the {Days} value is still wonky, the formula now produces the correct output:

I’m working on a similar issue - I have warranty products that need to show remaining warranty. Presently I have a date, and am happy for this to return in a days result as I can simply hide the number result and create a new field just dividing that result by 7. But I’m just getting constant errors when trying to use the DATETIME_DIFF - can anyone help?

What’s your current formula using DATETIME_DIFF()?

I had tried DATETIME_DIFF({Warranty Expiry}), TODAY() on the below to work out how many days are left on the warranty, see table excerpt below. I’m pretty new to this so not sure where I’m going wrong!

Your screenshot shows a date field and a number field, but no formula field. The formula you listed is written incorrectly, so my gut says you’re going from memory on an old test that you removed from the base. Either that, or you converted the formula field to a number field after the calculation was done.

The DATETIME_DIFF() function requires three arguments: two dates, and a units indicator as a string (e.g. “days”, “weeks”, etc.). To calculate how many days are left between today and a date from a date field, it should look like this:

DATETIME_DIFF(Date, TODAY(), "days")

When I use that on a date field with the dates you listed, here’s the result:

If you leave the formula field intact, it will update continuously with the remaining days on each warranty.

Ah excellent thanks!