+ Reply to Thread
Results 1 to 4 of 4

Birthdays

  1. #1
    Registered User
    Join Date
    04-01-2006
    Posts
    1

    Birthdays

    I keep details of my members at my skate park in a worksheet. Dates of birth are entered and then I am using the following formula to 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 the average age and also 2.) one which can easily identify who's birthday it is on todays date or better still a formulae which identifies members who's birthday is due in 30 days time (so we can offer birthday 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 skate free!

    Thanks

    Chris

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    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"

  3. #3
    Arvi Laanemets
    Guest

    Re: Birthdays

    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


    "Platski" <[email protected]> wrote in
    message news:[email protected]...
    >
    > I keep details of my members at my skate park in a worksheet. Dates of
    > birth are entered and then I am using the following formula to
    > 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 the
    > average age and also 2.) one which can easily identify who's birthday
    > it is on todays date or better still a formulae which identifies
    > members who's birthday is due in 30 days time (so we can offer birthday
    > 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 skate
    > free!
    >
    > Thanks
    >
    > Chris
    >
    >
    > --
    > Platski
    > ------------------------------------------------------------------------
    > Platski's Profile:

    http://www.excelforum.com/member.php...o&userid=33075
    > View this thread: http://www.excelforum.com/showthread...hreadid=528866
    >




  4. #4
    Mike Fogleman
    Guest

    Re: Birthdays

    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

    "Platski" <[email protected]> wrote in
    message news:[email protected]...
    >
    > I keep details of my members at my skate park in a worksheet. Dates of
    > birth are entered and then I am using the following formula to
    > 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 the
    > average age and also 2.) one which can easily identify who's birthday
    > it is on todays date or better still a formulae which identifies
    > members who's birthday is due in 30 days time (so we can offer birthday
    > 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 skate
    > free!
    >
    > Thanks
    >
    > Chris
    >
    >
    > --
    > Platski
    > ------------------------------------------------------------------------
    > Platski's Profile:
    > http://www.excelforum.com/member.php...o&userid=33075
    > View this thread: http://www.excelforum.com/showthread...hreadid=528866
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1