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!

[Elias_Gomez_Sai]

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

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

[Mike_McLaughlin]

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

[EMG]

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?

[EMG]

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.

[Mike_McLaughlin]

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

[EMG]

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?

[Elias_Gomez_Sai]

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

[EMG]

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.

[EMG]

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?