Formulas for Due Dates to Set a Status Field

Forum|Forum|4 years ago
October 4, 2021
1 reply
33 views

+2

Kimberly_Milruy
New Participant

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

+21

Justin_Barrett
Inspiring
Forum|Forum|4 years ago
October 6, 2021

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