Recurring formula for dates

Question

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 Answer

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:

Nathan_Ramey · Answer

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

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded