Send email automation based on date but at 9 AM only

[Troy_Gamble]

Hello!

I’m trying to set up an automation where a birthday email goes out on the client’s birthday. I found the solution on changing their DOB to current year (thank you community!) however, I don’t want the emails to go out before 9 AM on the day of their birthday. So far I’m adding in the “time” of 9 AM manually in the birthday field but I can’t seem to figure it out from there. Thanks in advance for your help! Troy

[TheTimeSavingCo]

Hey Troy, check this out. You can duplicate it to view the formulas and automations

I like your idea of adding the “time” of 9 AM in there, but think it might be easier to just do it via formulas instead.

Anyway, the idea is we have a field to check whether today’s the right day to send the email, and we have another field that gives us today’s date at 9AM. We then have another field that’ll check whether NOW() is past today’s date at 9AM.

We then trigger the automation if today’s the right day to send the email AND it’s past today’s date at 9AM.

I’ve separated all the fields out so that it’s easier to maintain and understand, but we could just as easily have it all in a single field as well

[Troy_Gamble]

Wow…thanks for being so quick to help…truly appreciate it! I’m not the best at the formulas so would you mind showing me the formulas for: 9 AM GMT Today, NOW() GMT and NOW() after 9 AM GMT today? Appreciate it!

[Troy_Gamble]

My apologies but I just realized that I don’t have my formula for “change birthday to current year” correct. I’m getting some with the year 2021 and some with 2022. Can you also provide your formula for this column as well? Much appreciated Adam!

[Troy_Gamble]

Aha! I re-read your reply and saw that I could duplicate the base and then see the formulas. I’m all good now. Again, appreciate your quick help Adam_C.

[Troy_Gamble]

Hi Adam - For some reason the “change birthday to current year” formula is showing results with the year 2021 vs 2022. Any ideas? Thanks

DATEADD(

Birthday,

DATETIME_DIFF(NOW(), Birthday, “Years”),

“Years”)

[TheTimeSavingCo]

Hi Troy! Glad you found it useful!

Hmm, may I have a screenshot of the issue, or perhaps you DM me a link to your base? I attempted to replicated this but was unsuccessful, apologies!

[Troy_Gamble]

Thanks Adam. In the base that you sent me, if/when I make a date change to the {Birthday}, the updated results in the {Change birthday to this year} says the year 2021 vs 2022. It’s correct upon original data entry so I can’t figure out what’s happening. I originally thought it had to do with leap years but that’s not the case.

[TheTimeSavingCo · Accepted solution]

Hi Troy, thank you very much for the screenshot! I’ve updated the formula for that field to the following:

DATEADD(
  Birthday,
  YEAR(NOW()) - YEAR(Birthday),
  "Years"
)

It should work properly now (I hope)

The issue was occuring as I misunderstood how DATETIME_DIFF works; the unit declaration only affects the display of the result and does not affect how the calculation is done

Apologies for the trouble!

[Troy_Gamble]

That fixed it! Thanks so much Adam…really appreciate your help. Have a great day.