Formula to count down days

No, you’re right: I brain-froze on that. The initial formula would return just the number of hours for all values `>=` 24 hours, but I described it wrong. The new one at least acts more predictably — I hope!

1 Like

Still, this was a terrific exercise for me. Thanks for your patience

Hi,

How to I countdown the amount of days I have left to complete a task given a {Due date}?

I tried WORKDAY_DIFF(TODAY(), {Due Date}) & IF(Days < 0, ’ days overdue ’, ’ days left ’)

but it lists things due today as ‘1 days left ’ but I would like that to say ‘Due Today’.

Would I need to nest another IF statement?

David

Welcome to the community, @David_Iken! Part of the issue might be the use of `TODAY()`. That returns the current date based on when the date changes in GMT, not your local timezone. I suggest using `NOW()` instead, which will include your local time.

To get a “Due Today” response, you will need to include another nested `IF()` function.

On a side note, what’s the “Days” reference in your formula? Is that another field that also calculates the date difference?

1 Like

Thanks for the response!

Yes, Days is another field:
(WORKDAY_DIFF(NOW(),{Due Date})-1)

Adding a -1 fixed the ‘due today’ problem, but do you know why the days overdue column value isn’t correct?

Here is the eq again for Time Left helper: WORKDAY_DIFF(NOW(), {Due Date})-1 & IF(Days > 0, " days left ", IF(Days < 0, ’ days overdue ’, ’ Due today ’))

Don’t worry about time left, that’s only for when it’s all finished and the only one that I’ll show.
IF(Status = ‘Complete’, ‘’, {Time Left helper})

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.

Ah, I didn’t catch that. That’s very strange. The `WORKDAY_DIFF()` function is supposed to not count weekends, but apparently it is. I’ll have to play with this later and see if I can figure out what’s going on.

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:

1 Like

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?

Thanks so much for looking into this and finding the workaround solution - I really appreciate it.

All the best,
David

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.