Calculate Number of Months and Days until Birthday

nak5120 0
Viewed 82

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.

datediff date match google-sheets arrays
2 Answers

try:

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

enter image description here

player00

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

enter image description here

Osm0
Related