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:
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:
- No {Next share} date apear though it should have added 14 days to the {Las shared} date
- 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?
- 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)
-
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). -
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.
