Adding formula fields for recurring dates, including SET_TIMEZONE

[Meagan_Caesar]

Hi, I'm sure the answer to this is really simple and I'm just missing it, but could someone please take a look at this formula and let me know why some records would be throwing an ERROR, but others are working? I'm thinking I've got something in the wrong order maybe?

We have a group events calendar set up and most events are one-off events, but we have a couple of recurring ones that I'd like to set up an Automation for to automatically generate the next event.

This is the formula I'm using:

DATEADD(

DATETIME_FORMAT(

SET_TIMEZONE(

{Date & Time}, 'Australia/Brisbane'), "DD/MM/YY hh:mm a"),

1, 'weeks')

Also, if I have a checkbox field that is ticked for 'Recurring Events,' how could I add an IF statement to check if that box is ticked first before generating the date, otherwise to leave the field blank? I've tried playing around with it, but I just keep getting errors, so it's time to ask all you amazing formula experts for some help.

Thank you!

[Stephen_Orr1]

Hi @Meagan_Caesar!
It's likely that the error is caused by an empty {Date & Time} field value.

You can add an IF check on this field to remove the error like:

IF({Date & Time},

DATEADD(

DATETIME_FORMAT(

SET_TIMEZONE(

{Date & Time}, 'Australia/Brisbane'), "DD/MM/YY hh:mm a"),

1, 'weeks'),

"")
This is saying, if {Date & Time} is populated, do the inner formula, otherwise, return blank (noted as "").

Checkbox fields evaluate to true or false so adding another IF statement to check whether a checkbox is checked would be:
IF({checkbox},

IF({Date & Time},

DATEADD(

DATETIME_FORMAT(

SET_TIMEZONE(

{Date & Time}, 'Australia/Brisbane'), "DD/MM/YY hh:mm a"),

1, 'weeks'),

""),

"")

Finally, this can be combined into a single IF statement using AND() like:

IF(AND({checkbox}, {Date & Time}),

DATEADD(

DATETIME_FORMAT(

SET_TIMEZONE(

{Date & Time}, 'Australia/Brisbane'), "DD/MM/YY hh:mm a"),

1, 'weeks'),

"")

Hope that helps!

-Stephen

[kuovonne · Accepted solution]

Using DATETIME_FORMAT() and SET_TIMEZONE() should be unnecessary.  You can set the timezone shown in the formatting options of the formula. 

IF(

  AND( {Date & Time}, {Recurring Events}),

  DATE_ADD({Date & Time}, 1, "weeks")

)

[Meagan_Caesar]

Thanks Stephen, this is great info and helps me better understand how to structure these sorts of formulas in the future. Weirdly, there weren't any missing dates, so I'm not sure what was throwing the error, but both your response and Kuovonne's work, so thank you.

[Meagan_Caesar]

Thanks Kuovonne, this works perfectly and is nice and simple.

[Stephen_Orr1]

Happy to help!

[Meagan_Caesar]

Hi @kuovonne and @Stephen_Orr1 ,

In addition to this question, I'm trying to set up a field to automatically generate due dates based on certain submissions. Here's what I have, but it won't format as a date, only as the string, so I'm not sure what's not working. It seems to only happen when I add the IF statement.

IF({Type of Edits}='Bio draft edits for Author revision', 

DATETIME_PARSE(

DATETIME_FORMAT(

DATEADD({Submitted},1,'week'),"DD/MM/YYYY"), 

"DD/MM/YYYY"),

" ")

Any help would be greatly appreciated!