Calculate amount of days between today and next birthday

Hey. I want to calculate the amount of days between today and the next birthday. In best case this works for dates without year too. I tried this where C2 is the birthdate:


but sadly this calculates the amount of days between today and last birthday, not next birthday. What do I have to change to get the amount of days between today and the next birthday?

Thanks for your help!

I wouldn’t trust in DATEDIF anyway.

If you can accept an error of 1 day in cases where the next birthday is in the next year and that year is a lepyear … you can use =DATE(YEAR(TODAY());MONTH(C2);DAY(C2))-TODAY()+IF(CURRENT()<0;365;0) for the birthday in cell C2. Don’t miss to set the result to an integer ‘Numbers’ format if wrongly a date format is displayed.

=DATEDIF(DATE(YEAR(TODAY())-200;MONTH(TODAY());DAY(TODAY()));C2;"yd") should also work as long as there are no persons above an age of 200 years in your list.

(Editing:) Also =DATEDIF(TODAY();DATE(YEAR(TODAY())+1;MONTH(C2);DAY(C2));"yd")

Wonderful, thanks for providing me with different solutions and add warnings if necessary.

If i might add another warning, the second formula ( with the -200 ) does not perform well for leap years. Try C2= the day before today, in a leap year. The third formula performs well.

Hello Nussturm,
Suppose the Date of Birth is inside cell C2, then you can compute the amount of days until his next birthday as follows:


Thanks to you too!


Does this help?

C2 being the birthdate, this computes his age in days…

Apologies - Misread Birthday/ Birthdate

no problem, i’ll upvote your comment as a small stimulation to keep on posting more answers in the future :smiley: