Skip to main content

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!

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:


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

Reply


Terms & Conditions