Skip to main content

Hi everyone! I am a beginner and was trying to modify other formulas I found on here but couldn’t get them to work. Would someone be able to help me nail down a formula that would check my due date field and then set a single select Urgency field depending on the due date for the following:

-Not Urgent (due dates that are farther than 1 month away from today’s date)

-Upcoming Due Date (for due dates that are 1 month out or less)

-Past Due (for due dates that are before today’s date)

-TBD (for any due date fields left blank)


Thanks!!!

Welcome to the community, @Kimberly_Milruya! :grinning_face_with_big_eyes:



A formula can only output data to the field where it’s defined. It can’t change the contents of any other field.


What I recommend doing is creating a formula field that calculates the difference between the due date and today:


IF({Due Date}, DATETIME_DIFF({Due Date}, TODAY(), "days"))

Future dates will return positive numbers, past dates will return negative numbers. I’ll call this field {Date Diff}. Use the output from that field (which can remain hidden, but I left it visible in the screenshot below just to show what it’s doing) in a {Status} formula field, along with a check to see if there’s a due date (for the “TBD” output):


IF(

  {Due Date},

  IF(

    {Date Diff} > 30,

    "Not Urgent",

    IF(

      {Date Diff} > 0,

      "Upcoming Due Date",

      IF(

        {Date Diff} < 0,

        "Past Due"

      )

    )

  ), "TBD"

)


You could even spice up the output strings with emojis for more immediate visual cues.

Reply


Terms & Conditions