+ Reply to Thread
Results 1 to 13 of 13

Forumla Help

  1. #1
    Registered User
    Join Date
    10-09-2018
    Location
    London
    MS-Off Ver
    Windows 10
    Posts
    5

    Forumla Help

    Hi Folks,

    Need some help, please.

    I need a formula. I have loads of people date of births, and what I require is which ones are over 50 years of age. How would i calculate this please?

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    4,355

    Re: Forumla Help

    Hello and welcome to the forum.

    You haven't given us much to go off of so naturally our answers may not be exactly what you are expecting.

    That being said, with birth dates in column A starting in A2, you can use this in B2: =IF(DATEDIF(A2,TODAY(),"y")>50,1,0)
    Then drag the formula down column B.

    Now you can then use a formula like this =SUM(B:B) to count the number of people that are over 50.

  3. #3
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    4,355

    Re: Forumla Help

    If you want to get it with a single formula, you can use this:

    =SUMPRODUCT(--(DATEDIF(A2:A100,TODAY(),"y")>50))

    Just adjust the range as needed.

  4. #4
    Forum Guru davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    XL 2010,2016
    Posts
    11,261

    Re: Forumla Help

    You have this in Outlook forum....

    If the birthday is in A1

    =(TODAY()-A1)/365.25
    =DATEDIF(A1,TODAY(),"Y")
    Last edited by davesexcel; 10-11-2018 at 08:33 AM.

  5. #5
    Registered User
    Join Date
    10-09-2018
    Location
    London
    MS-Off Ver
    Windows 10
    Posts
    5

    Re: Forumla Help

    Thank you all so much, very kind

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    4,355

    Re: Forumla Help

    You're welcome. Glad we could help.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  7. #7
    Registered User
    Join Date
    10-09-2018
    Location
    London
    MS-Off Ver
    Windows 10
    Posts
    5

    Re: Forumla Help

    Doh cant get any to work! Bascially if someone is over 5- i like the formula to say YES. For example if DOB is greater than 50 = yes

  8. #8
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    4,355

    Re: Forumla Help

    I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

    To upload an Excel workbook, follow these steps:
    1) Click on "Go Advanced"
    2) Click on "Manage Attachments"
    3) Click on "Choose File"
    4) Choose your file and click on "Open"
    5) Click on "Upload"
    6) Click on "Close this window"

  9. #9
    Registered User
    Join Date
    10-11-2018
    Location
    Gurgaon
    MS-Off Ver
    XP
    Posts
    10

    Re: Forumla Help

    I want to add numbers in Two coloum. I am not able to do this.

  10. #10
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    4,355

    Re: Forumla Help

    Quote Originally Posted by contactforinfo View Post
    I want to add numbers in Two coloum. I am not able to do this.
    Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

  11. #11
    Registered User
    Join Date
    10-09-2018
    Location
    London
    MS-Off Ver
    Windows 10
    Posts
    5

    Re: Forumla Help

    Thanks see attached
    Attached Files Attached Files

  12. #12
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    4,355

    Re: Forumla Help

    Just change 1 to "Y" and 0 to "N" in the formula from post #2.

    B2 =IF(DATEDIF(A2,TODAY(),"y")>50,"Y","N")

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    45,438

    Re: Forumla Help

    Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

    Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

    To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (note: this change is not optional )
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.

+ 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