Formula to count down days

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

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

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?

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.

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

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

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 ⏳')

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.

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(
    {Received},
    IF(
        DATETIME_DIFF(
            DATEADD(
                {Received},
                24,
                'hours'
                ),
            NOW(),
            'hours'
            )>=24,
        DATETIME_DIFF(
            DATEADD(
                {Received},
                24,
                'hours'
                ),
            NOW(),
            'hours'
            ),
        INT(
            DATETIME_DIFF(
                DATEADD(
                    {Received},
                    24,
                    'hours'
                    ),
                NOW(),
                'minutes'
                )/60
            )&' hrs'&
            IF(
                MOD(
                    DATETIME_DIFF(
                        DATEADD(
                            {Received},
                            24,
                            'hours'
                            ),
                        NOW(),
                        'minutes'
                        ),
                    60
                    )>0,
                ' '&
                MOD(
                    DATETIME_DIFF(
                        DATEADD(
                            {Received},
                            24,
                            'hours'
                            ),
                        NOW(),
                        'minutes'
                        ),
                    60
                    )&' min'
                )
        )
    )

Thank @Elias_Gomez_Sainz, I’ll play around with that.

@W_Vann_Hall Thanks so much! Worked beautifully. Took a little bit to sort out the magic in that one (beginner here). I can’t figure out where to insert a space between hours and minutes without breaking the formula. Right now, the output is: 23 hr35 min I’m missing something simple I’m sure.

Roughly halfway down in the formula, change:

        )&' hr'&

to

        )&' hr '&

        )&' hr'&

        )&' hr '&

Thanks so much, @Justin_Barrett. Embarrassed :slightly_smiling_face:

Oops — I’d fixed that in the scratch base I use for testing, but I guess I never updated the post. I’ve fixed it, now. (I put the additional space in a slightly different place than @Justin_Barrett, but either way is fine.)

Thanks @W_Vann_Hall - helpful for me to know what the different options are. Curious, what is the (tool?) used to break down/display more complex formulas as above?

I’m a big fan of Notepad++. Even though it doesn’t have an Airtable-specific formatting/highlighting definition available (I started building a User Defined Language file for Airtable a while back but became distracted) it does automatic pairing of parentheses and delimiter autocomplete out of the box; the first is absolutely invaluable for troubleshooting formulas ranging from annoyingly repetitive to complex to downright ridiculous. The downside is that you lose Airtable’s context-sensitive assistance in completing functions, but I doubt I could have written 3/4 of the formulas I have using only the configuration window.

It’s a Windows app, though, but there are Mac and Unix equivalents available.

If you try it, go to Settings > Preferences > Language and check the box in the lower right labeled ‘Replace [tabs] by space’. That way, when you paste indented code into Airtable’s formula configuration window, Airtable will ‘eat’ the whitespace when it displays the formula — but if you copy the formula from the configuration window and paste it back into NPP, the indentation is restored.

To use parenthesis auto-matching, place your cursor either before or after an opening or closing parenthesis: Both it and its matching pair are highlighted. No matching highlight? You have an unmatched delimiter, and Airtable will return an ‘invalid formula’ error.

I fondly remember Notepad++ from my Windows days. On the Mac, I use BBEdit. It has a lot of the same time-saving and headache-avoiding features that @W_Vann_Hall mentioned. Before that (and before Airtable), I used BBEdit’s slightly less beefy relative, TextWrangler, which got merged into BBEdit a couple years ago.

Thanks again @Justin_Barrett and @W_Vann_Hall. Lots to dig into here. I’m actually on Windows and Mac so both options are viable. I’m infinitely impressed with (and grateful for) the community and everyone’s help here. I picked a first project that quickly grew past 101 level, it’s been a great learning opp.

An interesting quirk: With this formula, until minute 1 has passed, the value is 24, not 24 hr 00 min. The Zap I have in place which now sends out a “you have xx hr, xx min left on your reservation” among other info grabs 24, nothing else. Odd as there’s typically a slight delay with zaps.

If I’m understanding the formula correctly >=24.... &' hr ' should be preventing that from happening, no?

I have resorted to appending a simple & ' left ⏳' at the end of the formula, as opposed to the original concept of {Time Left} & IF({Time Left} < 0, ' hrs overdue 💥', ' hrs left ⏳') The latter something I’d like to work out later.

Ah, you skipped the fine print! :winking_face: I mentioned I had based my formula on {Time Left} and not {TL}; as a result, anything 24 hours and greater ends up with no trailing ‘hrs’ at all.

Here’s a slightly fancier version of the routine; it will give you such results as the following:

46 hr
24 hrs
23 hrs 59 min
2 hrs 2 min
2 hrs
1 hr 59 min
1 hr 3 min
1 hr
0 hr 52 min

Edit: This would work better if I actually included the formula. :winking_face:

IF(
    {Received},
    IF(
        DATETIME_DIFF(
            DATEADD(
                {Received},
                24,
                'hours'
                ),
            NOW(),
            'hours'
            )>=24,
        DATETIME_DIFF(
            DATEADD(
                {Received},
                24,
                'hours'
                ),
            NOW(),
            'hours'
            )&' hrs',
        INT(
            DATETIME_DIFF(
                DATEADD(
                    {Received},
                    24,
                    'hours'
                    ),
                NOW(),
                'minutes'
                )/60
            )&
            IF(
                DATETIME_DIFF(
                DATEADD(
                    {Received},
                    24,
                    'hours'
                    ),
                NOW(),
                'hours'
                )<2,
                ' hr',
                ' hrs'
                )&
            IF(
                MOD(
                    DATETIME_DIFF(
                        DATEADD(
                            {Received},
                            24,
                            'hours'
                            ),
                        NOW(),
                        'minutes'
                        ),
                    60
                    )>0,
                ' '&
                MOD(
                    DATETIME_DIFF(
                        DATEADD(
                            {Received},
                            24,
                            'hours'
                            ),
                        NOW(),
                        'minutes'
                        ),
                    60
                    )&' min'
                )
        )
    )

Thanks @W_Vann_Hall for the fancier formula. It’s delightful :slightly_smiling_face: Didn’t think I missed the fine print, I just took…

will return ‘## hr’ for {Time Left} with values >= 24 hours

…to mean when an item is reserved – at the exact moment the 24-hour countdown begins / when {Time Left} equal to 24 hours – the value would be 24 hr, not 24.

But all set now. Thanks so much for your help!

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:

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

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