Birthdays

[Platski]

I keep details of my members at my skate park in a worksheet. Dates o
birth are entered and then I am using the following formula t
calculate ages:

=DATEDIF(J252,NOW(),"y") & " years, " & DATEDIF(J252,NOW(),"ym") &
months, " & DATEDIF(J252,NOW(),"md") & " days"

so that my answer reads ? years ? months, ? days.

What I would like to do is have 1.) a formula which calculates th
average age and also 2.) one which can easily identify who's birthda
it is on todays date or better still a formulae which identifie
members who's birthday is due in 30 days time (so we can offer birthda
party options 30 days in advance)

Any help would be appreciated as I am now getting out of my depth.

As a reward, if you are ever in Dubai, United Arab Emirates, come skat
free!

Thanks

Chri

 

[daddylonglegs]

It's difficult to do further calculations with the formula you quoted
because that returns a text result so to calculate the average age it's
best to calculate the average date of birth, e.g. if you have dates of
birth in J252:J300 then in *J302* use the formula

=AVERAGE(J252:J300)

then run your original formula on this average date of birth to give
the average age, i.e.

=DATEDIF(J302,NOW(),"y") & " years, " & DATEDIF(J302,NOW(),"ym") & "
months, " & DATEDIF(J302,NOW(),"md") & " days"

 

[Arvi Laanemets]

Hi

You can calculate the birthday in current year from birth date as
=DATE(YEAR(TODAY()),MONTH(Birthdate),DAY(Birthdate))
, or to cope with cases when bith date was 29. February
=MIN(DATE(YEAR(TODAY()),MONTH(Birthdate),DAY(Birthdate)),DATE(YEAR(TODAY()),
MONTH(Birthdate)+1,0))

Now, when you have the table of park members, you can use conditional
formatting to display rows in different colors, when some estimated number
of days is left to person bithday. Something like:
Display entries in a row red, when
=(DATE(YEAR(TODAY()),MONTH(Birthdate),DAY(Birthdate))-TODAY()=0)
Display entries in a row violet, when
=(DATE(YEAR(TODAY()),MONTH(Birthdate),DAY(Birthdate))-TODAY()>0 AND
DATE(YEAR(TODAY()),MONTH(Birthdate),DAY(Birthdate))-TODAY()<4)
Display entries green, when
=(DATE(YEAR(TODAY()),MONTH(Birthdate),DAY(Birthdate))-TODAY()>3 AND
DATE(YEAR(TODAY()),MONTH(Birthdate),DAY(Birthdate))-TODAY()<31)


Arvi Laanemets

 

[Mike Fogleman]

1) I came up with the same answer as daddylonglegs for average age.
2) Conditional format the first cell where your DATEDIF formula is, and then
drag down to the last formula:
Select Formula is and put this in the edit box =MONTH(A1)-MONTH(TODAY())=1
and then choose a format. This is done for the month, not 30 days, so the
condition will trigger between 28 - 31 days before a birthday.
Close enough?

Mike F