Creating a Subscription End date

Todd Smelser

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?

Heather Duff

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

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

Todd Smelser

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

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

Todd Smelser

It is now!!

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

Heather Duff

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

Todd Smelser

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

Todd Smelser

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

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

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

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

Todd Smelser

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

I think this needs to be more like:

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

No?

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?

Todd Smelser

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

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?