Adding a SOON or UPCOMING State to completed due dates


#1

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)


Create base for repeatable events?
#2

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​:🏃'
                )
            )
        )
    )

#3

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


#4

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

#5

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.


#6

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.


Need some help with conditional date formula (finding last day of month)!
#7

Thanks a lot. This is really helpful.