Help

Re: Create formula to detect if a date is next month

769 0
cancel
Showing results for 
Search instead for 
Did you mean: 
sarahdoodle
4 - Data Explorer
4 - Data Explorer

I run a career coaching program, and I am trying to automate how we offer people the opportunity to renew their time in the program.

We do this manually each month by looking at everyone’s dates and changing a single select field called “Initiate Renewal.”

What I want to do, is create a formula that will continuously look at everyone’s end dates and detect when someone’s end date is next month. If their end date is next month, it would update a field “Renewal end date is next month” with YES. And this would then be a trigger we could use in a Zap to send that person to the right email automation in ActiveCampaign (to send the renewal emails).

I made a mockup of what I’m trying to do. I’m totally open to better ways to do this, but this is what I’m trying to do on a very literal level :slightly_smiling_face: …

Screen Shot 2022-11-04 at 8.00.04 AM

2 Replies 2
49erAllie
5 - Automation Enthusiast
5 - Automation Enthusiast

You might also consider accomplishing this with a filter and triggering your automation when a new record appears under that filter

image

image

Here’s a formula that should do what you’re looking for:

IF(
  DATETIME_FORMAT(
    {Real End Date (Use This)},
    "MM YYYY"
  ) = 
  DATETIME_FORMAT(
    DATEADD(TODAY(), 1, 'months'),
    "MM YYYY"
  ),
  "Yes"
)

Since you’re using Zapier, @49erAllie’s solution is cleaner though