Skip to main content
Solved

Adding formula fields for recurring dates, including SET_TIMEZONE

M
Forum|alt.badge.img+7

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!

Best answer by kuovonne

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")

)

View original
Did this topic help you find an answer to your question?

6 replies

S
Forum|alt.badge.img+18

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
Forum|alt.badge.img+27
  • kuovonne
  • Brainy
  • 6002 replies
  • Answer
  • June 2, 2023

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")

)

M
Forum|alt.badge.img+7
  • Meagan_Caesar
  • Author
  • Known Participant
  • 32 replies
  • June 3, 2023
Stephen_Orr1 wrote:

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




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.

M
Forum|alt.badge.img+7
  • Meagan_Caesar
  • Author
  • Known Participant
  • 32 replies
  • June 3, 2023
kuovonne wrote:

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")

)


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

S
Forum|alt.badge.img+18

Happy to help!

M
Forum|alt.badge.img+7
  • Meagan_Caesar
  • Author
  • Known Participant
  • 32 replies
  • June 4, 2023
kuovonne wrote:

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")

)


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!

Reply


Most helpful members this week

Terms & Conditions