Upcoming dates, #error, and formatting

[QuietData]

I have a [Anniversary] field and a field that calculates the next anniversary, so I get reminders and write a card for my volunteers. Here is my problem:

The basic formula works fine when there is an anniversary in the field. When there isn't one, I get the #ERROR, which one would expect. I tried ISERROR(...) and IF(ISERROR...) to get it to show blank, but I can't get it to work. All the fields go blank, even if there is a date.

Here is the working formula in the AV_Next field:

DATETIME_PARSE(

DAY(Anniversary) & "-" &

MONTH(Anniversary) & "-" &

(YEAR(TODAY()) + IF(DATETIME_FORMAT(Anniversary, "MMDD") > DATETIME_FORMAT(TODAY(), "MMDD"),0, 1)),

"D-M-YYYY"

)

When I tried this, it worked for showing blanks, but then the date formatting ignored all normalcy. I get this: 2025-07-20T00:00:00.000Z

 IF(ISERROR(DATETIME_PARSE(

DAY(Anniversary) & "-" &

MONTH(Anniversary) & "-" &

(YEAR(TODAY()) + IF(DATETIME_FORMAT(Anniversary, "MMDD") > DATETIME_FORMAT(TODAY(), "MMDD"),0, 1)),

"D-M-YYYY")),"",DATETIME_PARSE(

DAY(Anniversary) & "-" &

MONTH(Anniversary) & "-" &

(YEAR(TODAY()) + IF(DATETIME_FORMAT(Anniversary, "MMDD") > DATETIME_FORMAT(TODAY(), "MMDD"),0, 1)),

"D-M-YYYY"))

When I tried this one to format the date, the #ERROR came back.

DATETIME_FORMAT(

IF(ISERROR(DATETIME_PARSE(

DAY(Anniversary) & "-" &

MONTH(Anniversary) & "-" &

(YEAR(TODAY()) + IF(DATETIME_FORMAT(Anniversary, "MMDD") > DATETIME_FORMAT(TODAY(), "MMDD"),0, 1)),

"D-M-YYYY")),"",DATETIME_PARSE(

DAY(Anniversary) & "-" &

MONTH(Anniversary) & "-" &

(YEAR(TODAY()) + IF(DATETIME_FORMAT(Anniversary, "MMDD") > DATETIME_FORMAT(TODAY(), "MMDD"),0, 1)),

"D-M-YYYY")),'MM-DD-YYYY')

[Mike_AutomaticN · Accepted solution]

Hey @QuietData!

Give the following formula a shot:

IF((Anniversary),
DATETIME_PARSE(
DAY(Anniversary) & "-" &
MONTH(Anniversary) & "-" &
(YEAR(TODAY()) + IF(DATETIME_FORMAT(Anniversary, "MMDD") > DATETIME_FORMAT(TODAY(), "MMDD"),0, 1)),
"D-M-YYYY"
),
BLANK())

As per my understanding, you are only receiving error if Anniversary is empty. With the above formula, you'd make such scenario blank rather than error, and otherwise you'll have your original formula.

Please let me know if that works for you!

Mike, Consultant @ Automatic Nation