Skip to main content

Multiple IF statements

  • February 20, 2024
  • 7 replies
  • 228 views
Tt25_2
Forum|alt.badge.img+3

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

P
Forum|alt.badge.img+9
  • Paul_Thompson
  • Known Participant
  • February 20, 2024

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.

Tt25_2
Dan_Montoya
Forum|alt.badge.img+17
  • Dan_MontoyaEmployee
  • Employee
  • February 20, 2024

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" )
P
Tt25_2
P
Forum|alt.badge.img+9
  • Paul_Thompson
  • Known Participant
  • February 20, 2024

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

Tt25_2
Tt25_2
Forum|alt.badge.img+3
  • Tt25_2
  • Author
  • New Participant
  • February 20, 2024

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

Tt25_2
Forum|alt.badge.img+3
  • Tt25_2
  • Author
  • New Participant
  • February 21, 2024

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
Forum|alt.badge.img+17
  • Dan_MontoyaEmployee
  • Employee
  • February 21, 2024

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" ))
TheTimeSavingCo
Forum|alt.badge.img+31

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:

 

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" )
Adam, Airtable Consultant - thetimesaving.company
Terms & ConditionsAccessibility statement