Editing a formula for upcoming due dates

Question

I need some help editing my original formula which is:
IF(IS_BEFORE({Actual Site Walk}, TODAY()), “ :white_check_mark: Done​:white_check_mark:”,IF(IS_BEFORE({Forecasted Site Walk}, TODAY()), “ :alarm_clock: OVERDUE​:alarm_clock:”,“ :running_man: In-progress​:running_man:”))
I’d like to add/change the formula to show “DUE SOON” for dates in the Forecasted Site Walk field that are within the next 14 days, but keep the rest as well.  How do I do that?  Thanks!

Justin_Barrett · Accepted Answer

I tested this, and am pretty sure it works for what you want:

IF(
    IS_BEFORE({Actual Site Walk}, TODAY()), "✅Done✅",
    IF(
        IS_BEFORE({Forecasted Site Walk}, TODAY()), "⏰OVERDUE⏰",
        IF(
            AND(
                DATETIME_DIFF(TODAY(), {Forecasted Site Walk}, "days") >= -14,
                DATETIME_DIFF(TODAY(), {Forecasted Site Walk}, "days") < 0
            ),
            "DUE SOON",
            "🏃‍♂️In-progress🏃‍♂️"
        )
    )
)

That worked! Thank you!! :grinning_face_with_smiling_eyes:

Jenni_Bowles · Answer

I tested this, and am pretty sure it works for what you want:

IF(
    IS_BEFORE({Actual Site Walk}, TODAY()), "✅Done✅",
    IF(
        IS_BEFORE({Forecasted Site Walk}, TODAY()), "⏰OVERDUE⏰",
        IF(
            AND(
                DATETIME_DIFF(TODAY(), {Forecasted Site Walk}, "days") >= -14,
                DATETIME_DIFF(TODAY(), {Forecasted Site Walk}, "days") < 0
            ),
            "DUE SOON",
            "🏃‍♂️In-progress🏃‍♂️"
        )
    )
)

That worked! Thank you!! :grinning_face_with_smiling_eyes:

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded