"Next Birthday" function

  • Thread starter Thread starter Kalabalana
  • Start date Start date
K

Kalabalana

Hey, I have an excel address book and I thought it would be nice to hav
a cell showing the next birthday, and another cell telling who's i
is.

I have all my dates lined up in a column

I'm unfamiliar with the excel functions, but I'm guessing to figure ou
this function, I need a way to store all the dates greater then th
current day, and then find the min in that list.
Storing them in a list is what has got me confused
 
Hi,

Your Names in column A
Your birthdates in column B

Next birthdate in any cell (let's say E9):

=MIN(MOD(DATE(YEAR(TODAY())+{0,1},MONTH(B1:Bx),
DAY(B1:Bx))-TODAY(),733))+TODAY()

Who's birthday is it:
=INDEX(A1:Ax,MATCH(DATEDIF(,E9,"yd"),DATEDIF(,B1:Bx,"yd"),0))

Replace Ax and Bx with proper row number in the two previous formulas.
Be sure to Ctrl-Shift-Enter them as they are ARRAY formulas.

Regards,

Daniel M.
 
hmmm, I just gave it a quick check, doesn't seem to work, gonna pic
apart the logic right now

could you explain your formulae? it goes beyond my excel experience
and my googling ability

ti
 
hmmm, I just gave it a quick check,

"Quick check"!
Not good enough: You have the responsability to check harder.
doesn't seem to work,
Click to expand...

Quite possible. So... HOW is it not working?:
You get wrong answers? With what data
You get error values?
Error entering Formula?

Give example(s) with expected and returned results.
could you explain your formulae?
Click to expand...

Sorry, ain't got enough time.
To understand formulae, you select part of it in the formula bar and press F9.
This will resolve that part.

Regards,

Daniel M.
 
I am extremely sorry, I made a mistake, thank you very much for th
info.
I was stuck on this problem for so long I was making a mistake over an
over again which I didn't realize, and I applied it to your function a
well.
You're functions work flawlessly, thank your for you time and work
 
Hi,

Try the V-lookup formula. (See Excel help - search
under "functions" - it will tell you how to get that
formula.)

Good luck
 
If you added another column that had the month and day of the birthday,
without a year, you could use a simpler formula. Let's say the birthdates are
in column D. In E2, put the formula =MONTH(D2)*100+DAY(E2) and copy it down.
Then the formula for the next birthday is

=MIN(IF(E2:E100>=MONTH(TODAY())*100+DAY(TODAY()),E2:E100,FALSE))

It's also an array formula, so needs to be entered with CTRL+SHIFT+ENTER.

Let's say that formula is in E110. For the person's name (in column A),
=INDEX(A2:A100,MATCH(E110,E2:E100,0))

If you don't want the extra column, you need Daniel's formulas.
 
hey if anyone doesn't mind, could someone explain what adding "{0,1}" to
the current year does, also why is the divisor 733 (is this suppose to
represent the days of 2 years added together)?
 
Hi Myrna,
In E2, put the formula =MONTH(D2)*100+DAY(E2) and copy it down.
Click to expand...

You meant obviously:
=MONTH(D2)*100+DAY(D2)
=MIN(IF(E2:E100>=MONTH(TODAY())*100+DAY(TODAY()),E2:E100,FALSE))
Click to expand...

It won't find the next birthday if there are no more occuring until the end of
Today's year (2004) whereas it should find the first one of the next year
(2005).

This probability increasing as TODAY() is reaching the end of the year.

Regards,

Daniel M.
 
Daniel,
Sorry, ain't got enough time.
To understand formulae, you select part of it in the formula bar and press F9.
This will resolve that part.
Click to expand...

I have been staring at your formula and trying various tests, highlighting
and pressing F9 etc for several hours. Do you have enough time to tell me
why 733 is the right number to be used in the MOD function? (By testing
various other numbers I know that it is but can't for the life of me see
why.)

Thank you in advance if you do take the time to enlighten me,

Regards

Sandy
 
Hi,
hey if anyone doesn't mind, could someone explain what adding "{0,1}" to
the current year does, also why is the divisor 733 (is this suppose to
represent the days of 2 years added together)?
Click to expand...

Why {0,1}:
Adding {0,1} generate two birthdates for every birthdate : one for this year and
one for next year.

If you substract TODAY(), the birthdates occuring EARLIER this year will
generate a negative number. If you MOD(X,733) this number, it will produce a
'high enough' number (more on that below) because when X is negative and Y
positive, MOD(X,Y) returns Y-X.

All in all, the net effect is to discard those Dates in the past when you want
to retrieve the MINimum. You will in fact get the minimum number of days that
are in the future (greater than today).
Then you add back TODAY() to generate the good date.

Why 733:
You got it right : I want to be sure to MODulo with a sufficiantly high number
than it won't give 0 on any days in the next 2 years. There are 731 max days
ahead (if there's a leap year) so 733 is a safe bet. If you want to use another
higher number, there is no problem.

Regards,

Daniel M.
 
Sandy,
I have been staring at your formula and trying various tests, highlighting
and pressing F9 etc for several hours. Do you have enough time to tell me
why 733 is the right number to be used in the MOD function? (By testing
various other numbers I know that it is but can't for the life of me see
why.)

Thank you in advance if you do take the time to enlighten me,
Click to expand...

Please see my response to Kalabalana.

Regards,

Daniel M.
 
There's a workaround: adding 1300 for dates earlier in the year and remove it
(via MOD) afterwards. It's an array formula.

=MOD(MIN(E1:E6+1300*(E1:E6<MONTH(TODAY())*100+DAY(TODAY()))),1300)

Regards,

Daniel M.
 
Back
Top