Adding a SOON or UPCOMING State to completed due dates

[Mike_Feldstein]

So I found this helpful article https://support.airtable.com/hc/en-us/articles/221104988-Conditional-formulas-with-emoji

and used the

IF({Completed} = 1, “ :white_check_mark: Done​:white_check_mark:”,IF(IS_BEFORE({Due date}, TODAY()), “ :alarm_clock: OVERDUE​:alarm_clock:”,“ :running_man: In-progress​:running_man:”))

AND

IF(Completed = 1, “”,IF(IS_BEFORE({Due date}, TODAY()), “ :alarm_clock: OVERDUE​:alarm_clock:”,""))

Formulas.

I am wondering how I could add a date range and have a DUE SOON stage. (instead of in progress, but conditional upon it being due within 3 days)

[W_Vann_Hall]

This should do it, I believe. (I’m afraid it’s not, strictly speaking, a variant on your existing formula; instead, I stole it from myself and tweaked it to fit your format.)

IF(
    {Completed}, 
    '✅Done​✅',
    IF(
        {Due date},
        IF(
            IS_AFTER(TODAY(),{Due date}),
            '⏰OVERDUE⏰',
            IF(
                IS_BEFORE(TODAY(),{Due date}),
                IF(
                    DATETIME_DIFF(TODAY(),{Due date},'days')<=3,
                    '📆DUE SOON📆',
                    '🏃In-progress​:🏃'
                    ),
                '🏃In-progress​:🏃'
                )
            )
        )
    )

[Mike_Feldstein]

So.

It worked, awesome. BUT - I am wondering if I can keep a WIP or “in
process”, or even “blank” But when the task is within 3 days, go to Due
Soon.

BTW<

thanks so much

[W_Vann_Hall]

Cerrtainly – just substitute the response you want for the appropriate response string.

In the formula I sent, to change the response for works in progress, I think all you have to change is the first entry of '🏃In-progress🏃'; IIRC, the second mention should never be encountered. If you want the field empty, you can substitute either an empty string ('') or the BLANK() function.

Or am I reading your question incorrectly?

(BTW, just noticed a needless colon (':') in the '🏃In-progress​:🏃' string. And I forgot to mention I added the IF({Due date}...) test simply to avoid an annoying #ERROR message if {Due date} is empty.)

I’m not sure how you’re using this, but I like to combine emoji flags with the actual date to provide a little more information. Here’s a version of the formula I gave earlier that embeds {Due date} in the response. (I haven’t tested it, but it should work.) To ensure proper alignment, the entry for normal, in-process dates uses the Em Space character (U+2003) to pad [roughly] the same amount of white space as an emoji.

IF(
    {Completed}, 
    '✅ '&DATETIME_FORMAT(Due date},'L')&' ​✅',
    IF(
        {Due date},
        IF(
            IS_AFTER(TODAY(),{Due date}),
            '🔥 '&DATETIME_FORMAT({Due date},'L')&' 🔥',
            IF(
                IS_BEFORE(TODAY(),{Due date}),
                IF(
                    DATETIME_DIFF(TODAY(),{Due date},'days')<=3,
                    '📆 '&DATETIME_FORMAT({Due date},'L')&' 📆',
                    '  '&DATETIME_FORMAT({Due date},'L')
                    ),
                '🔶 '&DATETIME_FORMAT({Due date},'L')&' 🔶'
                )
            )
        )
    )

[Maria_Dumitriu]

Hi. What about when the due date is not set?

The formula
IF({Completed} = 1, “ :white_check_mark: Done​:white_check_mark:”,IF(IS_BEFORE({Due date}, TODAY()), “ :alarm_clock: OVERDUE​:alarm_clock:”,“ :running_man: In-progress​:running_man:”))
works great, but I get #ERROR! in cells without deadlines, and I hate that.

Help.

[W_Vann_Hall]

Oh, in that case begin with a check to see if {Due date} is set.

If you look at the formula I gave in reply #4, you’ll see the third and fourth lines are IF({Due date}. That branch of the IF() statement is there simply to prevent those annoying #ERROR!" messages. Try wrapping your entire formula in the appropriateIF()` statement:

IF(
    {Due date},
    ***FORMULA GOES HERE***
    )

That will give you a blank field rather than an error when {Due date} isn’t set.

[Maria_Dumitriu]

Thanks a lot. This is really helpful.

[Jessica_Pena]

@W_Vann_Hall, I tried copy/pasting formula you had shared and for some reason, the Due Soon portion is showing up for not just tasks due within three days of the ‘Complete by:’ date, but there are things due in well over a month from now that now say “Due Soon”. Can you advise on where I went wrong?

IF(
{Progress Status},

IF(
    {Complete by:},
    IF(
        IS_AFTER(TODAY(),{Complete by:}),
        '🚨 OVERDUE 🚨',
        IF(
            IS_BEFORE(TODAY(),{Complete by:}),
            IF(
                DATETIME_DIFF(TODAY(),{Complete by:},'days')<=3,
                '📆 DUE SOON 📆',
                '🏃In-progress​:🏃'
                ),
            '🏃In-progress​:🏃'
            )
        )
    )
)