
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aug 24, 2022 08:43 AM
Hello everybody! I hope everyone is doing well.
I was trying to create a system where we have a due date for a item (in this case a bill), and then have a single select field with options to say how often we have to pay it. I then thought it would be great if I didn’t have to change the due date and that is done automatically.
However the formula I created isn’t correctly adding the days based on what I have in the single select field. It defaults to the first if statement.
My formula: IF(Frequency,“weekly”,DATETIME_FORMAT(DATEADD({Due Date},7,‘Day’),‘MM/DD/YYYY’),IF(Frequency,“bi-weekly”,DATETIME_FORMAT(DATEADD({Due Date},2,‘week’),‘MM/DD/YYYY’)))
Note: This is not all the option but I figured it was a problem with the structure and not the options.
Any help would be greatly appreciated. Thanks!
Solved! Go to Solution.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aug 24, 2022 11:18 AM
Made an additional edit below as the first solution didn’t solve your dynamic date issue.
Something like this could work.
Create two new fields (columns)
- Number - This would contain your IF statement or better yet SWITCH statement
- Unit - This would also be a switch statement
SWITCH Statements
Number Field:
SWITCH(
{Frequency},
'Weekly', 7,
'bi-weekly', 2,
etc..
)
Unit Field:
SWITCH(
{Frequency},
'Weekly', day,
'bi-weekly', week,
etc..
)
Then your Due Date formula could be
DATETIME_FORMAT(DATEADD({Due Date}, {number}, {unit}), 'MM/DD/YYYY')
I think this should work. Didn’t have time to test it.
You could then add an automation that has a condition of the original due date being ‘yesterday’
- Then update record with the new due date.
Base Setup:
Automation Setup:
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aug 24, 2022 11:18 AM
Made an additional edit below as the first solution didn’t solve your dynamic date issue.
Something like this could work.
Create two new fields (columns)
- Number - This would contain your IF statement or better yet SWITCH statement
- Unit - This would also be a switch statement
SWITCH Statements
Number Field:
SWITCH(
{Frequency},
'Weekly', 7,
'bi-weekly', 2,
etc..
)
Unit Field:
SWITCH(
{Frequency},
'Weekly', day,
'bi-weekly', week,
etc..
)
Then your Due Date formula could be
DATETIME_FORMAT(DATEADD({Due Date}, {number}, {unit}), 'MM/DD/YYYY')
I think this should work. Didn’t have time to test it.
You could then add an automation that has a condition of the original due date being ‘yesterday’
- Then update record with the new due date.
Base Setup:
Automation Setup:

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aug 25, 2022 07:14 AM
Thanks so much! I was going crazy trying to fit it into one field, never thought of putting it into separate fields.
