Formula to count down days

[nnnnneil]

Looking for a suggestion on how to best achieve the following:

I have an order date, say “4th July 2018” and i have 3 workdays in which to complete the order.

I’d like the formula to say “3 days left” or “2 days left” depending on today’s date.

WORKDAY({Order Received},3) will give me the due date. But how to calculate the number of days left until we hit the due date?

[W_Vann_Hall]

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

[Rasha]

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

[David_Iken]

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

[Justin_Barrett]

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

[David_Iken]

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})

[Justin_Barrett]

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.

David_Iken:

do you know why the days overdue column value isn’t correct?

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 💥")))

[David_Iken]

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.

[Justin_Barrett]

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.

[Justin_Barrett]

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:

[Stu_Kingston]

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?