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!
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
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
Hi Jonathan.
Wow, this is exactly what I was looking for. Would absolutely not have figured that out myself.
Thanks a lot!
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
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!
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!
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},
d...the IF formula here...]
)
JB
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},
y...the IF formula here...]
)
JB
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.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.