Help

Multiple IF statements

Topic Labels: Formulas
2106 7
cancel
Showing results for 
Search instead for 
Did you mean: 
Tt
5 - Automation Enthusiast
5 - Automation Enthusiast

I'm trying to create the following automated options in a project management base:

2+ days remaining
1 day remaining
due today!
1 day overdue
2+ days overdue

This is my attempt, but I'm struggling to work out how to write the multiple IF statements required to make it happen: 

IF((ISERROR((ABS(DATETIME_DIFF(TODAY(), {Milestone Date},'days')) & IF((DATETIME_DIFF(TODAY(), {Milestone Date},'days'))<0," Days Overdue", " Days Left"))))=1,"",(ABS(DATETIME_DIFF(TODAY(), {Milestone Date},'days')) & IF((DATETIME_DIFF(TODAY(), {Milestone Date},'days'))<0," Days left", IF((DATETIME_DIFF(TODAY(), {Milestone Date},'days'))=-1," day left", " Days overdue"))))
7 Replies 7
Paul_Thompson
6 - Interface Innovator
6 - Interface Innovator

OK, not an expert here, but I have written a few nested IF and SWITCH statements so in the absence of any other answers, try this:
To me, your use of brackets and '&' look more like an attempt to concatenate (join) separate answers rather than nest them. Try nesting your IF statements within the brackets rather than using '&' to produce another argument or expression. So without writing 'IF' again after the first argument, make another argument after the second comma.
An even better option could be not to use IF at all, but to utilise the more recently added "Switch" command. If you are not familiar with it, worth looking up. It breaks down such complicated nesting into a much easier to read and understand sequence and is supported by Airtable.

Dan_Montoya
Community Manager
Community Manager

Screenshot 2024-02-20 at 8.28.42 AM.pngScreenshot 2024-02-20 at 8.28.35 AM.png

SWITCH(  DATETIME_DIFF(TODAY(), {End date}, 'days'),
-2, "2+ days remaining",
-1, "1 day remaining",
0, "Due today",
1,  "1 day overdue",
2, "2+ days overdue"
)
Paul_Thompson
6 - Interface Innovator
6 - Interface Innovator

Thanks for that Dan, just what I was trying to suggest! Hopefully now Tt will simply be able to copy and paste.

Tt
5 - Automation Enthusiast
5 - Automation Enthusiast

Awesome, thankyou both! Switch is exactly what I needed, much simpler than nesting IF statements

Tt
5 - Automation Enthusiast
5 - Automation Enthusiast

Although it seems switch only works for discreet numbers, is there any way of specifying a range? I tried using <2 but I'm not sure if I'm getting the syntax correct

Dan_Montoya
Community Manager
Community Manager

Screenshot 2024-02-20 at 5.25.48 PM.png

If what you want is the actual number of days, then I would it with two fields

IF({Days Remaining}< 0, CONCATENATE(ABS({Days Remaining}), " days remaining"), 
IF({Days Remaining}> 0,CONCATENATE({Days Remaining}, " days overdue"), 
"Due Today" ))

Another way to deal with this would be to not nest your IFs at all, which I sometimes favour because it's just easier to read:

Screenshot 2024-02-21 at 11.42.21 AM.png

IF(
  Days = 0,
  'Due today!'
) &
IF(
  Days = -1,
  ABS(Days) & " day remaining"
) &
IF(
  Days < -1,
  ABS(Days) & " days remaining"
) &
IF(
  Days = 1,
  ABS(Days) & " day overdue"
) &
IF(
  Days > 1,
  ABS(Days) & " days overdue"
)