Skip to main content
Solved

Having automatically updating due date

  • August 24, 2021
  • 8 replies
  • 69 views
A
Forum|alt.badge.img

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

Best answer by augmented

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!

    E

    8 replies

    A
    Forum|alt.badge.img+18
    • augmented
    • Inspiring
    • Answer
    • August 25, 2021

    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!

    L
    R
    Forum|alt.badge.img+4
    • Rebecca_Elam
    • Inspiring
    • August 25, 2021

    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!


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

    Justin_Barrett
    Forum|alt.badge.img+21

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


    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.

    A
    Forum|alt.badge.img
    • Abby_Solomon
    • Author
    • New Participant
    • August 25, 2021

    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!


    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!

    R
    Forum|alt.badge.img+4
    • Rebecca_Elam
    • Inspiring
    • August 26, 2021

    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.


    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

    A
    Forum|alt.badge.img+18
    • augmented
    • Inspiring
    • August 26, 2021

    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!


    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.

    Justin_Barrett
    A
    Forum|alt.badge.img
    • Abby_Solomon
    • Author
    • New Participant
    • August 29, 2021

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

    Justin_Barrett
    Justin_Barrett
    Forum|alt.badge.img+21

    @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!

    Terms & ConditionsAccessibility statement