This is my first time posting here! Apologies if the explanation is not the most succinct.
I need help with creating a formula to work within an intricate email and list management system I've set up.
I have two tables:
Email --> Each record is an email being produced, tracking copy, design, and deployment
E.G. "Women's Board meeting 2 invite email"
List management --> Each record corresponds to a named list of contacts from our CRM.
E.G. "Women Board members"
Both of these tables are linked to each other
Each record in the email table is linked to at least 1 list record, indicating the audience the email is going to
Each record in the list table has 1 or more emails linked to it, indicating the past and future emails that have or will utilize this list's audience
Fields to be aware of
The Email table has a "Deploy date" field --> indicating when the email will drop
The List Management table has a look-up field pulling in the "Deploy date" data from the Email table
Since a list is usually linked to more than one email, the look-up field pulling in the "Deploy date" data contains multiple dates, not just one
We build our emails in advance, so the look-up field pulling in the "Deploy date" data can display dates from the past, and MULTIPLE dates in the future
(this happens when we plan to use the list record for many emails in the future)
I need a formula that will usethedata in the List Manager table's look-up field pulling in the Email table's "Deploy date" datato find the closest date to today in the future. In other words, find the most "upcoming" date of when the list record will be used for an email deployment.
E.G. Today is 04.21.23, the "deploy date" look-up field contains the dates
04.19.23 --> (Past date)
04.24.23 --> (Future date: closest to today, 04.21.23) I need the formula to pull out this date
After searching online for multiple solutions and coming up short, I've decided posting here may be the best way to help source this assistance. Very grateful for any and all suggestions this community may have. Please let me know if I can clarify the problem in any way, and thank you for your time!