Skip to main content

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"))))

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.


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"
)

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

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

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


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:

 

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"
)

Reply


Terms & Conditions