Anniversary formula is defaulting to GMT despite DATETIME_FORMAT being set

[Louise_Nighting]

I have created a formula to alert me to a birthday anniversary as well as upcoming anniversary (within 7 days), however I’ve noticed the date is GMT despite setting DATETIME_FORMAT as Los Angeles in the formula. At 17:00, the results change as it becomes 00:00 in GMT. This is causing a little confusion.

The date field the formula is pulling from doesn’t seem to be listed as GMT, so I don’t know why this might be happening. Have I done something wrong?

IF(
IS_SAME(
DATETIME_FORMAT(
SET_TIMEZONE(
DATEADD(
Birthday,
DATETIME_DIFF(
TODAY(),
Birthday,
‘years’
) + 1,
‘year’
),
‘America/Los_Angeles’
),
‘L’
),
TODAY()
),
“Anniversary”,
IF(
DATETIME_DIFF(
DATETIME_FORMAT(
SET_TIMEZONE(
DATEADD(
Birthday,
DATETIME_DIFF(
TODAY(),
Birthday,
‘years’
) + 1,
‘year’
),
‘America/Los_Angeles’
),
‘L’
),
TODAY(),
‘days’
) < 8,
“Almost Anniversary”
)
)

Thank you!

[Rose_K]

Hello @Louise_Nightingale!

You might want to double check your settings for both your Birthdate field and that formula’s formatting options to make sure the “Use the same time zone…” option is untoggled.

If that doesn’t fix it—could you possibly share a screenshot of your table setup so I can try to recreate it and see what might be the issue?

Let me know if I can help provide any more information, and if this helped answer your question please mark it as the solution :white_check_mark: .

[Louise_Nighting]

Hi @Rose_K, thanks for responding!

I can confirm that the “Use the same time zone” option is untoggled for the birthdate field.

I just realized that when I click formatting of the formula, I’m getting the following response: Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date.

The formula is to let me know the upcoming anniversary of a particular date. So the formula result is text, not a date or number. Could that be causing the issue?

[Justin_Barrett]

Another possible issue is that the first part of your formula is using the IS_SAME() function to compare a DATETIME_FORMAT() formatted date to TODAY(). The latter returns a date, but DATETIME_FORMAT() returns a string. Using IS_SAME() to compare a date to a string won’t work.

[Justin_Barrett]

Not sure why I didn’t catch this when I wrote the last comment, but the second half has a similar issue with DATETIME_DIFF() wrapped around DATETIME_FORMAT(). The former compares datetimes, the latter produces a string. In the end, none of this is going to work as expected.

If it weren’t so late, I’d try to work up an alternative, but I’m beat. I’ll see what I can do tomorrow morning, but I can’t make any promises.