Formulas for Due Dates to Set a Status Field

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! :smiley:

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"
)

Screen Shot 2021-10-05 at 8.13.13 PM

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

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.