Having automatically updating due date

[Abby_Solomon]

Hi There,

I’m struggling to figure out a formula (if there is a suitable one available), and wondering if anyone can help. I have a spreadsheet to track subscriptions and when the next orders are due to go out - these are done at regular intervals; either 4-weekly, 6-weekly, 8-weekly or 3-monthly. Rather than having to update the next due date of the subscription for each order I would love to have a formula that can automatically update the next due date based on the start date e.g. if the order started on 01/07/21 on a 3-monthly subscription, then it would be able to update the next due date to 01/10/21. I would then like it to continue updating automatically, so when we get to 01/10/21 it would automatically update to 01/01/21. Is this possible? Or anything similar that doesn’t require it to be a completely manual process. Ideally I would like it to be dependent on the subscription frequency e.g. 4-weekly or 3-monthly from another column, however if this is not possible, I would be happy to create separate columns for each duration e.g. one column for 4-weekly with that set formula, one for 3-monthly etc.
Please let me know if any of this is unclear. Any assistance would be greatly appreciated!

Thanks so much,
Abby

[augmented · Accepted solution]

Hi Abby. Totally doable. You will need an automation, also. First, you need a formula field that calculates the next due date. It could look something like below.

IF({Frequency}!='3-monthly',
     DATEADD({Order Date},
                        SWITCH({Frequency},
                               '4-weekly',4,
                               '6-weekly',6,
                               '8-weekly',8),
                         'weeks'),
     DATEADD({Order Date}, 3 ,'months')
)

There would be other ways to accomplish this, but you get the idea. It’s a little more work at first, but you probably want to have another field that keeps the original order date (maybe a simple created on field if the orders come in from a form or other automation).

The second part to getting this to work is the automation. Depending on your workflow (when you no longer mind if the {Order Date} field gets updated} you have an automation that looks at your {Next Due Date} field and triggers when it is “today”. The action is simply replacing {Order Date} with {Next Due Date}.

Anyway, this is just off the top of my head. Good luck!

[Rebecca_Elam]

I. DIDNT. KNOW. you could switch in a dateadd. why didn’t I think of that

[Justin_Barrett]

Rebecca_Elam:

I. DIDNT. KNOW. you could switch in a dateadd.

Function nesting can occur anywhere. It’s most often done by nesting IF() functions inside each other, but it’s easy to forget that others can be nested as well. As long as you know that the output of a given function matches the input requirements of another, creative nesting can be used to more efficiently solve lots of problems.

[Abby_Solomon]

Thank you so much for you help, that saved me so much time with the formula! Really appreciate it.
Sorry to call on more advice, but the only trouble I’m coming into is when trying to set-up the automation. It won’t let me select the Next Due Date as a field to replace the Order Date with. Because it’s a formula it’s not selectable at this step. Any more advice as to how I could get around this?

Thanks again!

[Rebecca_Elam]

i should say it never occurred to me to use it like that ive been writing nested ifs nested in a bigger switch formula. never thought to just switch those too haha

[augmented]

Hi Abby. Hmm…I just tried it in my scratch table automation and I could select my formula field. Do you have your formula field formatted in a similar manner to your date field? It shouldn’t matter if you’ve been able to format it as a date or date/time.

[Abby_Solomon]

I think I may have sorted out the issue. Thank you so much for all your help!

[Justin_Barrett]

@Abby_Solomon Glad to know that you got the answer you were seeking! If you would, please mark the appropriate comment above as the solution to your question (if one of those helped guide you to a solution). This helps others who may be searching with similar questions. Thanks!