Find the date that is the last date before another date (closest but not after)

Topic Labels: Dates & Timezones Formulas
375 0
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

I could not find a solution for this in the forums: From a list of dates, I need to find that date that is the last date on that list before the launch date of a given project. The idea is to find the date best suited for an event to happen before a project is launched.

Full description:

Records in table "Status" are each linked to one specific "Project" and one specific "Outlet". "Status" looks-up the launch date of that project. In "Outlets", multiple dates are rolled-up from a table called "Release dates".

I am looking for a formula that returns the one "Release date" that is closest, but not after the Launch date of the Project. The returned date must be in the "Status" table.

I cannot use conditional look-up, as the date I need to compare with is not relative to today, but referenced elsewhere. So no way to use a relative condition like "before today" or so.

I have transformed the dates in to Unix timestamps already. Mathematically, the date to be returned is the minimum positive value of {Launch Date} minus {Release Date}. If the result is negative, then the release date is after the launch which is not desired.

I hope this all makes sense. Would be thankful for your help!


0 Replies 0