Formula help - Next share date

[AnnicaT]

Hello everyone!
I have a need for a field that determines next “share time” for an event based on specific criteria.
I have the following columns:
{Start date} = date field
{End date} = date field
{Select dates} = checkbox field that indicates when an event concists of only select dates between the {Start date} and {End date} and not the whole span. I also have a date field for
{Last shared} = date field

What I want to achieve is a new field, using formula, that gives me the date the event should be shared next called {Next share}.

• If the difference between {Start date} and {End date} is less than 14 days AND the {Select dates} is unchecked AND if today is before {End date} I want it to add 7 days to the {Last shared} date.
• If the difference between {Start date} and {End date} is greater than 14 days AND the {Select dates} is unchecked AND if today is before {End date} I want it to add 14 days to the {Last shared} date

Idealy I would like it to additionally return the following:

• If the event doesn’t have an {End date} I want it to simply say “Single event”
• If the {Select dates} is checked I want it to return “Share at next occurance”.
  We do not catalogue the inbetween dates when an event has only select dates, so a message like above is preffered.

I am beyond greatful for any and all help I may get as I am new to Airtable.
I am able to find my way around easier formulas, but the multiple conditions here have me stumped.

[David_Skinner]

IF(AND(DATETIME_DIFF({End date},{Start date}, ‘days’) < 14,
{Select dates} = 0,
DATETIME_DIFF({End Date}, TODAY(), ‘days’) > 0),
DATEADD({Last shared}, 7, ‘days’){Last shared},
IF(AND(DATETIME_DIFF({End date},{Start date}, ‘days’) > 14,
{Select dates} = 0,
DATETIME_DIFF({End Date}, TODAY(), ‘days’) > 0),
DATEADD({Last shared}, 14, ‘days’){Last shared},
BLANK())
)

That should sort out the first part. I personally wouldn’t use a formula that can output a date and a string, but if you must then this should work:

IF( {End date}=BLANK(), “Single Event”,IF({Select dates}=1,“Share at next occurance”,IF(AND(DATETIME_DIFF({End date},{Start date}, ‘days’) < 14,
DATETIME_DIFF({End Date}, TODAY(), ‘days’) > 0),
DATEADD({Last shared}, 7, ‘days’){Last shared},
IF(AND(DATETIME_DIFF({End date},{Start date}, ‘days’) > 14,
DATETIME_DIFF({End Date}, TODAY(), ‘days’) > 0),
DATEADD({Last shared}, 14, ‘days’){Last shared},
BLANK())
)))

I haven’t tested these, so there may be some typos.

[AnnicaT]

Thank you so much for these.
I tried 'em out and somethings off as they are and I’m honestly too tired to figure out just what right now, it’s been a looong day, but I will deffinetely have a better look in the morning and see if I can figure it out.

What are the drawbacks having the same formula return a date output and a string?

Anyway, I just really wanted to thank you for taking time to answer.

[David_Skinner]

Sorry, there were a couple of typos. These work:

IF(AND(DATETIME_DIFF({End Date},{Start Date}, “days”) < 14, {Select dates} = 0, DATETIME_DIFF({End Date}, TODAY(), “days”) < 0), DATEADD({Last shared}, 7, “days”), IF(AND(DATETIME_DIFF({End Date},{Start Date}, “days”) > 14, {Select dates} = 0, DATETIME_DIFF({End Date}, TODAY(), “days”) < 0), DATEADD({Last shared}, 14, “days”), BLANK()) )

And then:

IF( {End date}=BLANK(), “Single Event”,IF({Select dates}=1,“Share at next occurance”,IF(AND(DATETIME_DIFF({End Date},{Start Date}, “days”) < 14,
{Select dates} = 0,
DATETIME_DIFF({End Date}, TODAY(), “days”) < 0),
DATEADD({Last shared}, 7, “days”),
IF(AND(DATETIME_DIFF({End Date},{Start Date}, “days”) > 14,
{Select dates} = 0,
DATETIME_DIFF({End Date}, TODAY(), “days”) < 0),
DATEADD({Last shared}, 14, “days”),
BLANK())
)))

In general I would want the output of any formula field to be consistent. Mainly because if you want to subsequently make calculations on the data in those fields you might not be able to. Particularly if you are going to be rolling up data into another table, or even if you just wish to look at the sum or average at the bottom of the table.

A problem more specific to what you are trying to achieve here is that you cannot format the {Next share} field as a date if it can also be a string. This means that your dates will be stuck formatted in ISO format like this:

rather than this:

[AnnicaT]

Thank you again for taking time to answer and explaining to me.
Now that I understand the issue I agree that both a date and string output is less than ideal. If nothing else for the reason to avoid the pesky date formate, but the added reasons makes it a no-brainer.

When using the second version of the first formula you provided I run into the following:

1. No {Next share} date apear though it should have added 14 days to the {Las shared} date
2. It returns a date that is after the End date.

Unfortunately my brain has taken a vacation due to a combination of illness and lack of propper sleep making me stare dumbly at the formula instead of being able to figure it out.

Additionally:

Since having a formula return a date and a string is a bad idea, what is the best way to work around this of the suggestions below?

1. Adding a field for status that

• if the event is a single event (lacks {End Date}) and has no {Last shared} returns “-”.
• if the event is a single event (lacks {End Date}) and has a {Last shared} returns “ :white_check_mark: ”
• if the event is an event that generates a {Next share} and that date is before {End Date} returns “ :ballot_box_with_check: ”
• if {Next share} is before TODAY returns “ :red_circle: ”
• if the event is an event that generates a {Next share} and TODAY is after {End Date} returns “ :white_check_mark: ”
• (I think I covered all needed eventualities, but as my brain refuses to fully cooperate, if you spot something missing, please do add)

2. Is there a way to, by in one field only log the dates the {Select event} takes place, have the next date in that series minus three days be displayed in the {Next share} field?
   (As we have a free account and a lot of records, we don’t want to add to the amount of records by creating and linking a table to log the dates as I’ve seen suggested elsewhere on this forum, so the work around has to sork without adding records).

3. Some other genius solution that I haven’t even thought of.

If anyone has the kindness in their heart to help with any of these questions I would be beyond greatful, especially since I am currently unable to digest and apply new information to solve the problem on my own.