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


            )


        )


    )


)