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?

[Rasha]

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.

[Justin_Barrett]

Roughly halfway down in the formula, change:

        )&' hr'&

to

        )&' hr '&

[Rasha]

Thanks so much, @Justin_Barrett. Embarrassed :slightly_smiling_face:

[W_Vann_Hall]

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

[Rasha]

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?

[W_Vann_Hall]

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.

[Justin_Barrett]

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.

[Rasha]

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.

[W_Vann_Hall]

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

[Rasha]

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!