IF formula for date closest to TODAY

[EMG]

Hi, I’m trying to automate a “status/priority” field based on “start/end date” fields - assigning one of the following options to every record:

• NOW
• NEXT
• LATER
• DONE

I was able to create an IF-formula for 3 of the 4 options:
IF(Start <= TODAY(), IF(End >= TODAY(), “NOW”, “DONE”), “LATER”)

In the LATER group, I would now like to categorize the date closest to TODAY as NEXT but can’t seem to find a way to do this.

Any tips? Thanks!

[Mike_McLaughlin]

this is tricky to do cleanly or at all, as you need to compare all of the record values for the field against one another. Which is more of a spreadsheet function than database. I was trying to do something with rolling up into another table, but its too limited still to get the ‘next’ trip that way either.

@Elias_Gomez_Sainz is right, as long as the view is filter by ‘later’ sorted A-Z, the top one will be ‘next’