# 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:

=DATEDIF(C2;TODAY();"yd")


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?

edit retag close merge delete

Sort by » oldest newest most voted

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

more

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

( 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.

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

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:

=DATEDIF(TODAY();IF(TODAY()>DATE(YEAR(TODAY());MONTH(C2);DAY(C2));DATE(YEAR(TODAY())+1;MONTH(C2);DAY(C2));DATE(YEAR(TODAY());MONTH(C2);DAY(C2)));"d")

more

Thanks to you too!

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

Hallo

 =EDATE(C2;CEILING(MONTHS(C2;TODAY();0);12))-TODAY()

more

=DATEDIF(TODAY(),C2 ,"d")

Does this help?

more

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

( 2017-05-20 15:27:39 +0200 )edit
1

( 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

( 2017-05-20 15:54:58 +0200 )edit