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

                )

            )

        )

    )

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

                )

            )

        )

    )

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

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 eroughly] 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')&' '

                )

            )

        )

    )

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

The formula

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

works great, but I get #ERROR! in cells without deadlines, and I hate that.

Help.

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.

Thanks a lot. This is really helpful.

@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​:'

            )

        )

    )

)