Oct 04, 2021 01:33 PM
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!!!
Oct 05, 2021 08:16 PM
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.