IF formula for date closest to TODAY


#1

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!


#2

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

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


#3

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


#4

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?


#5

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.


#6

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

image


#7

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?


#8

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


#9

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.


#10

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?


#11

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’