Ask Your Question

Calculate amount of days between today and next birthday

asked 2017-05-20 12:42:05 +0200

NussTurm gravatar image

updated 2017-05-20 14:12:44 +0200

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!

edit retag flag offensive close merge delete

4 Answers

Sort by » oldest newest most voted

answered 2017-05-20 14:59:56 +0200

Lupp gravatar image

updated 2017-05-20 15:14:02 +0200

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")

edit flag offensive delete link more


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

NussTurm gravatar imageNussTurm ( 2017-05-20 21:45:33 +0200 )edit

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.

librebel gravatar imagelibrebel ( 2017-05-20 22:15:57 +0200 )edit

answered 2017-05-20 15:31:26 +0200

librebel gravatar image

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:

edit flag offensive delete link more


Thanks to you too!

NussTurm gravatar imageNussTurm ( 2017-05-20 21:47:18 +0200 )edit

answered 2017-05-21 00:24:54 +0200

karolus gravatar image


edit flag offensive delete link more

answered 2017-05-20 14:48:33 +0200

ianbattison gravatar image


Does this help?

edit flag offensive delete link more


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

librebel gravatar imagelibrebel ( 2017-05-20 15:27:39 +0200 )edit

Apologies - Misread Birthday/ Birthdate

ianbattison gravatar imageianbattison ( 2017-05-20 15:41:55 +0200 )edit

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

librebel gravatar imagelibrebel ( 2017-05-20 15:54:58 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-05-20 12:42:05 +0200

Seen: 448 times

Last updated: May 21 '17