# Recurring formula for dates

Topic Labels: Formulas
Solved
1327 2
cancel
Showing results for
Did you mean:
6 - Interface Innovator

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.

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
8 - Airtable Astronomer

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:

2 Replies 2
8 - Airtable Astronomer

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:

6 - Interface Innovator

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