# Countdown to upcoming birthdays

Topic Labels: Formulas
Solved
3148 5
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

Hey. I’ve tried searching for solutions here, but haven’t found anything I was able to implement.

What I’m trying to do is listing all my close friends and relatives, and creating a field that shows the number of days until their next birthday. Then I’d sort it by 1-9 so that the next birthday is on top.

When the birthday is over, the “days until” should then jump back to 364, and therefor be sorted to the bottom.

Could anyone assist me here? Thanks!

1 Solution

Accepted Solutions
13 - Mars

Hi @Rasha - you can say:

``````IF(
{Birthday},
[...the IF formula here...]
)
``````

What this does is only evaluate “Days from today” IF “Birthday” is present. You can do a similar thing for the “Days to next birthday” formula:

``````IF(
{Days from today},
[...the IF formula here...]
)
``````

JB

5 Replies 5
13 - Mars

Hi @Sebastian_Juhola - you can do this:

The field `{Days from today}` is:

`DATETIME_DIFF(DATETIME_FORMAT(DATETIME_PARSE(DATETIME_FORMAT(Birthday, 'MM/DD') & '/' & DATETIME_FORMAT(TODAY(), 'YYYY')), 'MM/DD/YYYY'), TODAY(), 'days')`

What this does is take the month and day from the actual birthday and joins it with the current year to get the date of the birthday this year, then does a diff on that and `TODAY()`

From there, you can add another field which adds 365 to the “days from today” if it is negative, otherwise takes the number as it stands (if positive):

`IF({Days from today} < 0, {Days from today} + 365, {Days from today})`

Obviously, this isn’t doing anything with leap years :winking_face:

JB

5 - Automation Enthusiast

Hi Jonathan.

Wow, this is exactly what I was looking for. Would absolutely not have figured that out myself.

Thanks a lot!

6 - Interface Innovator

How do you prevent a NaN value in Days from today and Days to next Birthday if you don’t want/need to enter a Birthday? Thanks!

13 - Mars

Hi @Rasha - you can say:

``````IF(
{Birthday},
[...the IF formula here...]
)
``````

What this does is only evaluate “Days from today” IF “Birthday” is present. You can do a similar thing for the “Days to next birthday” formula:

``````IF(
{Days from today},
[...the IF formula here...]
)
``````

JB

6 - Interface Innovator

Thanks, @JonathanBowen! I almost had it but with an extra bracket. And looks like adjusting the first formula takes care of the Days to next birthday field.