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!
Thanks
Martin
