Help

Combining two IF formulas to create a single "Due in X Days" formula

Topic Labels: Formulas
Solved
Jump to Solution
640 2
cancel
Showing results for 
Search instead for 
Did you mean: 
ellllllllllle
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi! I'm new to creating formulas. I can create two IF formulas in independent columns, but am struggling to combine them into a single formula to communicate a task is due in ___ days, but to also make this countdown stop when a task is completed.

This formula is dependent on two fields: Due Date and Status (Not Started, In Progress, Done).

I need this formula to create 3 potential outcomes:

  1. BLANK, if no Due Date was entered. 
  2. __ DAYS, if a Due Date was entered and the task is not complete.
  3. ✔️if a Due Date was entered and the task is complete.

I achieved #1 and #2 with this formula: IF({Due Date}, DATETIME_DIFF({Due Date}, TODAY(),'days') & " days")

I achieved #3 with this formula: IF(Status="Done", "✔️")

But I am not sure how to combine these into one single formula, in one single column. 

1 Solution

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

Give this a try:

IF(
  Status="Done", 
  "✔️",
  IF(
    {Due Date}, 
    DATETIME_DIFF(
      {Due Date}, 
      TODAY(),
      'days'
    ) & 
    " days"
  )
)

Screenshot 2023-06-12 at 1.48.04 PM.png

Link to base 

See Solution in Thread

2 Replies 2
TheTimeSavingCo
18 - Pluto
18 - Pluto

Give this a try:

IF(
  Status="Done", 
  "✔️",
  IF(
    {Due Date}, 
    DATETIME_DIFF(
      {Due Date}, 
      TODAY(),
      'days'
    ) & 
    " days"
  )
)

Screenshot 2023-06-12 at 1.48.04 PM.png

Link to base 

Perfect, thank you!