Recurring formula for dates

[Nathan_Ramey]

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!

[Zack_S · Accepted solution]

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)

1. Number - This would contain your IF statement or better yet SWITCH statement
2. 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:

[Nathan_Ramey]

Thanks so much! I was going crazy trying to fit it into one field, never thought of putting it into separate fields.