Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Feb 19, 2024 09:49 PM
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:
Feb 19, 2024 10:40 PM
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.
Feb 20, 2024 08:29 AM
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"
)
Feb 20, 2024 08:39 AM
Thanks for that Dan, just what I was trying to suggest! Hopefully now Tt will simply be able to copy and paste.
Feb 20, 2024 02:54 PM
Awesome, thankyou both! Switch is exactly what I needed, much simpler than nesting IF statements
Feb 20, 2024 05:13 PM
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
Feb 20, 2024 05:26 PM
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" ))
Feb 20, 2024 07:43 PM
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"
)