Hello.
My birthday is January 14, 1978 (1978-01-14) and I entered it into A2 cell.
What function should I use that calculates the number of days until the birthday in each year without having to update the function every year?
Thank you.
Birthday.ods (10.7 KB)
A similar function is described here:
Calculating age in Calc
Maybe you can adapt it for your purposes.
I found this function and works:
=DATE(YEAR(A2)+DATEDIF(A2,B2,"y")+1,MONTH(A2),DAY(A2))
and this function for days until to next birthday:
=DATEDIF(B2,C2,"d")
I’m sorry. My formula is invalid if the birthday is 29th February.
Replace all occurrences of TODAY() with DATE(2023;3;1) and give a leap day as argument. You will see that the next birthday in 2024 from the view point of 2023-3-1 is calculated as 2024-3-1 instead of 2024-2-29.
=EDATE(A1;(YEARS(A1;TODAY();0)+1)*12) # returns "next" Birthday
<… above …> -TODAY() # returns the days from now
Next birthday B1:
=DATE(
YEAR(TODAY())
+(TODAY()>
DATE(
YEAR(TODAY())
;MONTH(A1)
;DAY(A1)
)
)
;MONTH(A1)
;DAY(A1)
)
which is today’s year, birthday’s month and birthday’s day + one year if today is later than this year’s birthday.
Days until next birthday =B1-TODAY()
EDIT (simplified explanation):
DATE(year(today());month(A1);day(A1)) returns this year’s birthday.
The comparison
( today() > this_years_birthday )
returns 1 if this year’s birthday already happened this year and 0 it is in the future.
We add the result of the comparison to the year number of this year’s birthday.
For those born on the 29th of February in a leap year, I suppose it is a personal choice when to celebrate your birthday in a non-leap year. Happily, you have a choice of which formula:
IHMO its totally ok for those Leap year-impaired to celebrate 2 days long 