Setting a status when today is between two subscription dates

Platform discussions
, ,
1

I have a board which lists subscription start and end dates. I want a status column to tell me if today is in between these two dates (ie, if subscription is current). I’ve messed about with various automations and some formulas but not getting there. Any help greatly appreciated!

2

Hi Tim,

So a Formula column will definitely get you there, and with conditional formatting, you’ll be able to add some color to make that visually helpful. However, Formula columns are limited as you can’t use them as triggers or conditions for automations, which is a common frustration.

If you need to use a Status column for that reason, your only option would be to create an integration with Zapier or make.com (or another similar tool). If you want some help with that, feel free to reach out to me (flyingravendesign@gmail.com).

If a Formula would be enough, here’s one you can try (don’t forget to change the names of your start and end date columns):

IF(
    AND(
        FORMAT_DATE(TODAY(),"YYYYMMDD")>=FORMAT_DATE({Start Date},"YYYYMMDD"),
        FORMAT_DATE(TODAY(),"YYYYMMDD")<=FORMAT_DATE({End Date},"YYYYMMDD")
    ),
    "Subscription Active",
    "Subscription Inactive"
)

Here’s an alternative that tells you whether a subscription is past or future:

IF(
    FORMAT_DATE(TODAY(),"YYYYMMDD")>=FORMAT_DATE({Start Date},"YYYYMMDD"),
    IF(
        FORMAT_DATE(TODAY(),"YYYYMMDD")<=FORMAT_DATE({End Date},"YYYYMMDD"),
        "Subscription Active",
        "Subscription Expired"
    ),
    "Subscription Pending"
)

Then follow that up with your conditional formatting to make the cell or row a different color, depending on your preference.

3

Thanks Francis, that’s really helpful.

I’ll go with the formula option … i wasn’t a million miles off but got my syntax wrong.

Tim

1 Like