# Formula to count down days

Topic Labels: Formulas
18249 35
cancel
Showing results for
Did you mean:
8 - Airtable Astronomer

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?

35 Replies 35
5 - Automation Enthusiast

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

13 - Mars

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

8 - Airtable Astronomer

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?

13 - Mars

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.

6 - Interface Innovator

:wave: @Elias_Gomez_Sainz - I combined snippets of your formula here with mine and got close to resulting what I need. But wondering if you can help with the final stretch?

I have a Received field set to Date (created time) for incoming reservations via a form. I want to set up a field for a 24-hr countdown (the max hold time, after which inventory is released).

Option 1 - I’m not sure what to substitute for WORKDAY to account for all 7 days, which makes this inaccurate.

`DATETIME_DIFF((WORKDAY({Received},1)),TODAY(),'hours') & " hours left"`

Option 2 - A slightly different approach.

`DATETIME_DIFF((DATEADD({Received},24,'hours')),TODAY(),'hours') & " hours left"`

The ideal output would be :hourglass_flowing_sand: XX hours, XX minutes left. This field would be visible to buyers.

Thanks for any tips!

13 - Mars

If you want non work days as valid, you use the second option with `DATEADD`:

6 - Interface Innovator

Thanks! I worked off of this reference for this formula and others, but have yet to figure out how to get the minute value in there for an item that expires in under 24 hours. Also, with `Today()` is this formula, the countdown remained static - not sure why. I changed it to NOW() and it seems to work now.

Here’s what I have:

I want to add Minutes to:

Time Left (for internal reference)

`DATETIME_DIFF((DATEADD({Received},24,'hours')),NOW(),'hours')`

TL (Visible on website and used for email zap)

`{Time Left} & IF({Time Left} < 0, ' hrs overdue 💥', ' hrs left ⏳')`

13 - Mars

You could check if Time Left is under 24 hours, and then add a similar formula for minutes, substracting the the hour from Time Left. Another idea is to use seconds as the unit and then format it as Duration, so you’d get like `17:34`.

13 - Mars

This will return ‘`## hr`’ for `{Time Left}` with values `>=` 24 hours and ‘`## hr`’ or ‘`## hr ## min`’ for values `<` 24 hours, depending on whether the amount of time left is in whole or fractional hours. I derived it from your `{Time Left}` fomula because it was there; it should be easily modifiable for your particular purpose.

``````IF(
IF(
DATETIME_DIFF(
24,
'hours'
),
NOW(),
'hours'
)>=24,
DATETIME_DIFF(
24,
'hours'
),
NOW(),
'hours'
),
INT(
DATETIME_DIFF(
24,
'hours'
),
NOW(),
'minutes'
)/60
)&' hrs'&
IF(
MOD(
DATETIME_DIFF(
24,
'hours'
),
NOW(),
'minutes'
),
60
)>0,
' '&
MOD(
DATETIME_DIFF(