You can add another IF instead, comparing for instance the next 7 days with Start:

IF(DATEADD(TODAY(),7,'days') >= Start, 'NEXT','LATER')

are you trying to only have a single record in the table show up as ‘next’?

Thought about this option as well since I’ve used it to organize my task list but doesn’t really work in this case. I’m trying to automatically order my trips/travels, so I can easily see what my “NEXT” trip is and which trips come “LATER” this year.

Thanks for the tip though, any other suggestions?

Normally it’ll be a single record, yes - namely a single trip/travel - but it could be there are more trips in one day, so they’d all have to be marked as NEXT unless I would use TIME as well as DATE.

I’m not easily envisioning how your sheet functions unless you have a lot of date fields, but the below should solve the problem as described for comparing 2 dates within a single record to today’s date and picking off the closer one.

IF(DATETIME_DIFF(TODAY(),{Start},‘days’)>DATETIME_DIFF(TODAY(),{End},‘days’),{End},{Start})

Sorry if I’m not describing it well. I’ll try giving an example of what I’d like to accomplish:

• Start: 1/1/2019 - End: 7/1/2019 = DONE



• Start: 15/1/2019 - End: 7/2/2019 = NOW



• Start: 14/2/2019 - End: 20/2/2019 = NEXT



• Start: 1/3/2019 - End: 7/3/2019 = LATER



• Start: 17/3/2019 - End: 27/3/2019 = LATER



• Start: 1/4/2019 - End: 7/4/2019 = LATER

So I’ve got a bunch of start dates later than TODAY and would like to mark the first in line as NEXT and all others as LATER.

Hope this gives you a better idea of what I’m looking for?

You can just create View to hide past Travels, and order future by Date. The first is the next.

Ultimately, I’d like to setup a kanban-view with four stacks (NOW/NEXT/LATER/DONE) instead so I can have a complete, yet compact overview. Hence I need to find a way to indicate which record comes right after NOW, so I can mark it as NEXT and prefer this to happen automatically with a formula instead of making manual changes.

Think I got one step closer by creating a new field that calculates the amount of days between TODAY and all LATER start dates. Now I just need a way to mark the lowest number as NEXT. Any thoughts?

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’