Creating a Subscription End date

Hi all,

Trying to come up with a formula to advance a date by the appropriate duration based on an entry in an order form.

For instance, When the customer orders a subscription for one year (Annually in the attached example), I would like to have the subscription end date calculate 365 days from the subscription start date. Similarly, if the duration is monthly, I would like the end date to be 30 days from the start date.

Is something like that possible?


Best Answer

«1

Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    Hi @Todd Smelser ,


    Try something like this:

    =IF([Subscription Duration]@row = "Monthly", [Subscription Start Date]@row + 30, IF([Subscription Duration]@row = "Annually", [Subscription Start Date]@row + 365, ""))

    Be sure both your Subscription Start Date and your Subscription End Date columns are formatted as date columns.


    Hope this helps. Let me know if it works!


    Best,

    Heather

  • Hi, Thank you so much! However, I get "Invalid Column Values".

    Could this be due to the fact that we are mixing text/number columns with a Date Column?

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    @Todd Smelser Is your end date column also set as a date column?

  • It is now!!

    (I'm a dope) Thank you so much for your help.

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    😊 Those tiny, hidden settings sometimes make all the difference! Glad it worked. Have a great week.

  • Hi @Heather Duff , Let me see if I can challenge you a bit further. I would like to take your formula and append it to another argument that would advance the Start date automatically if certain conditions are not met.

    For instance,

    =IF(AND[Subscription Start Date]@Row = Today(), [Subscription Status]@row <> "Active"), IF([Subscription Duration]@row = "Monthly", [Subscription Start Date]@row + 30, IF([Subscription Duration]@row = "Annually", [Subscription Start Date]@row + 365, ""))

    This, obviously, is not working but what I want to do is, if the Subscription start date is reached, and the sub has not been cancelled, advance the start date using the formula you provided above.

    Any Ideas?

    Todd

  • This could be easier than I am thinking. Maybe I just set the Start date to today if today is reached and the status is not Cancelled

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    Try this: =IF(AND([Subscription Start Date]@row <= TODAY(), [Subscription Status]@row <> "Cancelled"), IF([Subscription Duration]@row = "Monthly", [Subscription Start Date]@row + 30, IF([Subscription Duration]@row = "Annually", [Subscription Start Date]@row + 365, "")))

  • Todd Smelser
    Todd Smelser ✭✭✭
    edited 01/31/22

    I think that will work but I think we can make this simpler by setting the start date as today if the sub is not cancelled +1 day

    =IF(AND([Subscription Start Date]@Row = Today() +1, [Subscription Status]@Row = "Active", Today()).

    This is giving my UNPARSEABLE though

    Still working it....

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    =IF(AND([Subscription Start Date]@Row = Today() +1, [Subscription Status]@Row = "Active"), TODAY())

  • Hi,

    That was my second try but I still get "Unparseable"

    =IF(AND([Subscription Start Date]@Row = Today() +1, [Subscription Status]@Row = "Active"), TODAY())

  • Todd Smelser
    Todd Smelser ✭✭✭
    edited 01/31/22

    I think this needs to be more like:

    IF(Today() = Today() +1, [Subscription Status]@Row = "Active"), TODAY()))

    No?

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    Oh! I missed the +1. Put the 1 in the TODAY() parenthesis:

    =IF(AND([Subscription Start Date]@Row = TODAY(1), [Subscription Status]@Row = "Active"), TODAY())


    That being said - which column are you putting this formula in?

  • Still unparseable. I also had the wrong cell in the formula so I did make a substitution in your formula:

    =IF(AND([Subscription End Date]@Row= TODAY(1), [Subscription Status]@Row = "Active"), TODAY())

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    You may run into some issues with circular references. If the end date is calculated off of the start date, and you have a formula in the start date column that references the end date, you'll end up chasing your tail.

    Does that make sense?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!