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! :wink:

1 Like

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

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 :boom:’, ’ days left :hourglass_flowing_sand:’)

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

Would I need to nest another IF statement?

Thanks in advance!
David

Welcome to the community, @David_Iken! :smiley: 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

Hi @Justin_Barrett,

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 :hourglass_flowing_sand:", IF(Days < 0, ’ days overdue :boom:’, ’ Due today :alarm_clock:’))

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’, ‘:white_check_mark:’, {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:

Screen Shot 2020-09-16 at 3.19.32 PM

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:

Screen Shot 2020-09-16 at 3.23.51 PM

“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.

Screen Shot 2020-09-16 at 3.24.03 PM

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:

Screen Shot 2020-09-16 at 3.41.33 PM

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?

Hi @Justin_Barrett,

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!

image

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:

Screen Shot 2020-09-18 at 6.32.27 PM

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