Calculating days to next birthday in Google Spreadsheet

I have the date of birth of a person and want to calculate the days until his/her next birthday. How to do this in a way to deal with leapyears开发者_Python百科 and other "odd" things?

Using google spreadsheet internal functions:

=IF(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))>TODAY(),
  DATE(YEAR(TODAY()),MONTH(A2),DAY(A2)),
  DATE(YEAR(TODAY())+1,MONTH(A2),DAY(A2)))
-TODAY()

where A2 is the cell with the birthday of the person.

The IF at the beginning is for testing if the next birthday is this year or next year.

A slightly shorter neater version using pure sheets functions is:

=DATE(YEAR(A2)+DATEDIF(A2,TODAY(),"y")+1,MONTH(A2),DAY(A2))-TODAY()

This simpler, and uses the DATEDIF(...,"y") to get complete years that have passed and adds one, rather than needing an If around the entire calculation.

Having figured this out working on the problem myself, I then came across the same solution on this site, which includes some more explanation.

following the nice and short version that Jon Egerton posted, here is a small change that returns 0 when the birthday is on the current day (instead of returning 365), and also handles future dates

=DATE(YEAR(A2)+IF(A2<TODAY(),DATEDIF(A2+1,TODAY(),"y")+1,-DATEDIF(TODAY(),A2,"y")),MONTH(A2),DAY(A2))-TODAY()

I found that using WolframAlpha for calculating the days is the most "simple" way to do it. Use the following code:

REGEXEXTRACT(JOIN("";ImportXML(JOIN("";"http://www.wolframalpha.com/input/?i=birthday+";YEAR(A2);"-";MONTH(A2);"-";DAY(A2);"&asynchronous=false&equal=Submit"); "//script")); "(\d+) days until next")

Where A2 is the cell with the birthday of the person.