Formula to count down days


#1

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?


#2

I don’t have a formula for you because I’m not sure about the “WORKDAY” function you’re using, but try: yourdatehere - TODAY()


#3

Use DATETIME_DIFF() with your Due Date and TODAY().


#4

Great that works! The formula i’m now using is:

DATETIME_DIFF((WORKDAY({Order Received},3)),TODAY(),'days') & " days left"

which results in:

-18 days left
-18 days left
0 days left
0 days left
1 days left
2 days left
2 days left

I’d like any negative results to show “-18 days overdue” instead of -18 days left". I’m thinking I need an IF statement but would appreciate any thoughts on how to do it?


#5

I would create an additional Field to add the text, something like:

  • Days field with the formula: DATETIME_DIFF((WORKDAY({Order Received},3)),TODAY(),'days')
  • Status (or similar), with the IF() and the text: Days & IF(Days < 0, ' days overdue 💥', ' days left ⏳')

I’ve made the formula here so maybe it’s not correct, but you get the idea. You could have both formulas together, but you’d have to replace Days with the same formula twice. You can hide the Days field so you only see the Status field. Also, having a separate field with the days, allows you to order the records by that value, which I think is super useful.