Calculate Number of Months and Days until Birthday-CodePudding

I have a list of everyone's birthdays like below where today is 2022-09-09.

Birthday
January 15
July 1
December 25

How do I create a formula in google sheets where regardless of the year, I can figure out how many months are days are left before the birthday values?

It gets tricky because July 1 we know has already passed since it is September.

I would like to ideally do this in google sheets.

CodePudding user response：

try:

=ARRAYFORMULA(IFERROR(
 DATEDIF(TODAY(), A2:A4, {"M", "MD"}), 
 DATEDIF(TODAY(), YEAR(A2:A4) 1&TEXT(A2:A4, "-mm-dd"), {"M", "MD"})))

CodePudding user response：

Solution

Use this formula

=ARRAYFORMULA(IF(A2:A="",,
 DATEDIF(TODAY(), 
         IF(
              DATE(YEAR(TODAY()),
      MONTH(REGEXEXTRACT(A2:A&"", "([A-Za-z]. ?) ")&1),
      REGEXEXTRACT(A2:A&"", "[0-9] "))<=TODAY(),
              EDATE(
              DATE(YEAR(TODAY()),
      MONTH(REGEXEXTRACT(A2:A&"", "([A-Za-z]. ?) ")&1),
      REGEXEXTRACT(A2:A&"", "[0-9] ")),12),
              DATE(YEAR(TODAY()),
      MONTH(REGEXEXTRACT(A2:A&"", "([A-Za-z]. ?) ")&1),
      REGEXEXTRACT(A2:A&"", "[0-9] "))), {"M", "MD"})))