Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculating a date with a DOB value (Excel 2003)

    And another! Am trying to calculate age using a DOB value. I figured that Today minus DOB would get me what I needed, but it returns an actual date, and I just need an age like "58" Is this a formatting or do I have the concept of my formula wrong?

    Thanks again - I'm all questions today (and yesterday, for that matter)

    Satiria

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Calculating a date with a DOB value (Excel 2003)

    If the DOB is in A1:

    <pre>=DATEDIF(A1,TODAY(),"y")</pre>


    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating a date with a DOB value (Excel 2003)

    If DOB is in A1, then the formula below will give the age in years:

    <code>
    =DATEDIF(A1,TODAY(),"Y")
    </code>
    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating a date with a DOB value (Excel 2003)

    Legare to my rescue again. Thank you. That DATEDIF function worked great to return an individual age for each person, which is what I needed. However, when I attempt to then average all the values in the Age column, I'm also getting a date, rather than a number. I'm using the standard AVERAGE function and then highlighting the range of cells containing the ages we just calculated with the DATEDIF function.

    -Sat.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating a date with a DOB value (Excel 2003)

    Simply clear the formatting (Edit | Clear | Formats) from the cell with the average.

  6. #6
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating a date with a DOB value (Excel 2003)

    Sweet! Thanks Hans.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating a date with a DOB value (Excel 2003)

    A date in Excel is actually just a number, with special formatting applied to display it as a date. Excel tries to be smart and automatically formats a number as a date if it seems appropriate; sometimes it guesses wrong.

  8. #8
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating a date with a DOB value (Excel 2003)

    Thanks Hans, for the clarification. Since I didn't see the DATEDIF function under Insert Function, could someone breakdown what that formula is doing for me? I typed it manually and it works great...I just wanted to know the logic behind it.

    Sat.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating a date with a DOB value (Excel 2003)

    Chip Pearson provides an excellent reference for this function (that is undocumented in Excel itself): DATEDIF Function.

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating a date with a DOB value (Excel 2003)

    Calculates the number of days, months, or years between two dates.

    DATEDIF(start_date,end_date,unit)

    The unit parameter is:

    "Y" The number of complete years in the period.
    "M" The number of complete months in the period.
    "D" The number of days in the period.
    "MD" The difference between the days in start_date and end_date. The months and years of the dates are ignored.
    "YM" The difference between the months in start_date and end_date. The days and years of the dates are ignored.
    "YD" The difference between the days of start_date and end_date. The years of the dates are ignored.
    Legare Coleman

  11. #11
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating a date with a DOB value (Excel 2003)

    Thank you both. That is why you guys are Moderators and I am just a lowly 3StarLounger <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

Posting Permissions

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