Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Recurring formula for dates

Topic Labels: Formulas
Solved
Jump to Solution
270 2
cancel
Showing results for 
Search instead for 
Did you mean: 

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!

1 Solution

Accepted Solutions

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:
image

Automation Setup:
image

image

See Solution in Thread

2 Replies 2

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:
image

Automation Setup:
image

image

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