Expiration During Next Month

Airtable Questions
1

I struggle with formula writing.

I have a table of our astronomy club members. Each member has an expiration date.

I have a list view where I need to show any member that has an expiration date that occurs during the next calendar month. (Not simply 30 days from now)

So if we are in August, our membership officer can look at the list and see which members need to be contacted about renewal because their membership expires in September.

My reading says I need a formula that checks and sets a value to true, so that my list view can retrieve records with only true for expiring next month.

Was hoping this could be done just with the built in filters but I don’t think it will.

It’s so hard not having the mind for this!

2

I would create a new column called “Months to Expiration” and use

MONTH({Expiration Date}) - MONTH(TODAY())

and then filter for when Months to Expiration is 1.

Note: I don’t love using TODAY() because it’s resource-intensive, but I’m not sure there’s a way around it for this use case.

1 Like
3

This won’t work when the months cross years. For example, when the {Expiration Date} is in January, but the current date is in December.

@AstroChris I handle situations like this by having a formula field that truncates the date to the first of the month, like an {Expiration Month} field that is the first on the month. Then I setup the view to only show records where the {Expiration Month} is in the next 31 days. There are a few days of the month where I will get both the next month’s records AND two month’s out. However, I also group by the {Expiration Month} field, and since a human is processing the records (versus an automation), it is easy for the human to just ignore the records for two months out.

4

@AstroChris

Here’s a simple formula you could use to show whether the expiration date occurs during the next month:

IF(

  YEAR({Expiration Date})=YEAR(TODAY()),
  IF(MONTH({Expiration Date})-MONTH(TODAY())=1,
  "Next Month"),

IF(
  YEAR(TODAY())+1=YEAR({Expiration Date}),
  IF(MONTH({Expiration Date})-MONTH(TODAY())=-11,
  "Next Month")
  )

)
5

Thank you all. Scott, this worked well, thank you. Though today is August 31, it’s returning only those expiring records from October 2023, which is not next month until tomorrow.

Edit: I changed the =1 to =0 and it returned records that expire in September. Not sure why LOL.
August minus September = -1 and not 0

7

Ahh, that is likely a time zone issue.

It might be easiest to create a separate formula field representing today’s date, and then you can set the time zone individually on that field (plus your expiration date field as well).

Then, back in your main formula field, you would only reference the fields themselves instead of using the TODAY() function.

If that doesn’t work, it can get more complex to solve… it would require more tweaking of the formula field using Date/Time functions to force the TODAY() function to evaluate as a certain time zone.

Under some circumstances, it might even require adding more formula fields. My time zone base covers some of the issues with time zones, which is an area that Airtable makes challenging.

8

Thank you Scott. I will tinker with that. For now, I’m assuming that will happen at last day of the month only and therefore isn’t a time sensitive issue.

9

It might be a quick fix… I would start by checking the time zone settings on your Expiration Date field.

11

I agree with Scott that it is probably a timezone issue.

But Scott’s formula still uses TODAY(), which I prefer to avoid because it causes every record in the table to recalculate every day, even if nothing else has changed. I would rather shift that “today-ness” to the view filter, so the formula doesn’t need to recalculate, and Airtable might have done some indexing under the hood to make processing less resource intensive.

If you really want a formula that uses TODAY(), you can also try this one.

IF(
  DATETIME_FORMAT(
    DATEADD(TODAY(), 1, "month"),
    "YYYYMM"
  )
  =
  DATETIME_FORMAT({Expiration Date}, "YYYYMM"),
  "Expires next month"
)

If time zones really bother you, you could throw in SET_TIMEZONE() into the mix for both date fields.

But I would still prefer having a formula field that doesn’t use TODAY()

12

Do I add the Time Zone formula to the Expiration Date field? And does that mean it becomes a formula field where it used to be a human’s data entry field?

13

No. Keep your editable field an editable field. Use SET_TIMEZONE() inside DATETIME_PARSE()`. But it gets tricky because TODAY() is GMT midnight, so you might not have to use SET_TIMEZONE() depending on where you live and when your working hours hare. SET_TIMEZONE() would probably work better with NOW(), but NOW() is even worse than TODAY().

I don’t feel like going down this particular rabbit hole and teasing out the exact use of SET_TIMEZONE() because I don’t agree with the original premise of using TODAY().

14

Try this one:

ExpirationDate: member expiry date.
ExpiredDate: LEFT(DATESTR(ExpirationDate), 7)
NextMonth: LEFT(DATESTR(DATEADD(DATETIME_PARSE(LEFT(DATESTR(TODAY()), 7) & "-02"), 1, "month")),7)
IsExpired: IF(ExpiredDate=NextMonth, "Expires next month", "")

This should not be affected by timezones because it’s using the second of the month and then comparing months after removing the day.

1 Like