+ Reply to Thread
Results 1 to 7 of 7

Need help calculating age field but want blank if nothing is entered in DOB field

  1. #1
    Registered User
    Join Date
    06-02-2014
    Posts
    15

    Need help calculating age field but want blank if nothing is entered in DOB field

    I need help figuring out how to write the formula to calculate an age on a form I'm creating, however, I want the age to display nothing if there isn't anything in Date of Birth field.

    For instance:

    I have a Date of Birth (DOB) field and an Age field. I want the age to calculate if there is something entered in the DOB field. If the DOB field is blank I want the Age field to be blank.

    Here's what I've come up with by using this Excel Forum website, however, cannot figure out how to get the age field to be blank if nothing is in the DOB field... Currently the DOB field is blank and it is showing "114" in the Age field.

    =IF(MONTH(TODAY())>MONTH(P7),YEAR(TODAY())-YEAR(P7),IF(AND(MONTH(TODAY())=MONTH(P7),DAY(TODAY())>=DAY(P7)),YEAR(TODAY())-

    Thanks!!f

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,721

    Re: Need help calculating age field but want blank if nothing is entered in DOB field

    It's simpler to use DATEDIF, e.g.

    =IF(P7="","",DATEDIF(P7,TODAY(),"y"))

    DATEDIF calculates the current age......but if P7 is blank the formula returns a blank
    Audere est facere

  3. #3
    Registered User
    Join Date
    06-02-2014
    Posts
    15

    Re: Need help calculating age field but want blank if nothing is entered in DOB field

    Perfect - thanks! But does it matter about the leap year thing? That is why the other one was so long I think...because whomever wrote it was factoring in the leap year.

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,721

    Re: Need help calculating age field but want blank if nothing is entered in DOB field

    Your formula doesn't seem to be complete so I can't say exactly what it does.......but DATEDIF will take account of leap years. The only issue might be if P7 is 29th Feb. Using DATEDIF will assume that the birthday is "celebrated" on 1st March in non leap years......but I think your version will probably do that too......

    .....if you want to retain the current formula then the principle is still the same for returning a blank - modify your formula as follows

    =IF(P7="","",original_formula)
    Last edited by daddylonglegs; 06-02-2014 at 03:55 PM.

  5. #5
    Registered User
    Join Date
    06-02-2014
    Posts
    15

    Re: Need help calculating age field but want blank if nothing is entered in DOB field

    Here's what Excel corrected the original formula to be:

    =IF(MONTH(TODAY())>MONTH(P7),YEAR(TODAY())-YEAR(P7),IF(AND(MONTH(TODAY())=MONTH(P7),DAY(TODAY())>=DAY(P7)),YEAR(TODAY())))

    When I try to modify per your suggestion I get a "too many arguments" error. Did I modify incorrectly?
    =IF(P7="","",MONTH(TODAY())>MONTH(P7),YEAR(TODAY())-YEAR(P7),IF(AND(MONTH(TODAY())=MONTH(P7),DAY(TODAY())>=DAY(P7)),YEAR(TODAY())))

  6. #6
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,721

    Re: Need help calculating age field but want blank if nothing is entered in DOB field

    Quote Originally Posted by helphelpme View Post
    =IF(MONTH(TODAY())>MONTH(P7),YEAR(TODAY())-YEAR(P7),IF(AND(MONTH(TODAY())=MONTH(P7),DAY(TODAY())>=DAY(P7)),YEAR(TODAY())))
    I don't think the formula is correct if you want to get age in all cases - Given the current date (2nd June 2014) then if P7 = 1st June 2000 the formula returns the current year, 2014 - if P7 = 4th June 2000 then you get FALSE

    This version should work in all cases, following the same logic

    =IF(MONTH(TODAY())>MONTH(P7),YEAR(TODAY())-YEAR(P7),IF(AND(MONTH(TODAY())=MONTH(P7),DAY(TODAY())>=DAY(P7)),YEAR(TODAY())-YEAR(P7),YEAR(TODAY())-YEAR(P7)-1))

    and to modify that to return a blank if P7 is blank.....

    =IF(P7="","",IF(MONTH(TODAY())>MONTH(P7),YEAR(TODAY())-YEAR(P7),IF(AND(MONTH(TODAY())=MONTH(P7),DAY(TODAY())>=DAY(P7)),YEAR(TODAY())-YEAR(P7),YEAR(TODAY())-YEAR(P7)-1)))

    ......but I think that will return identical results to my original suggestion with DATEDIF

  7. #7
    Registered User
    Join Date
    06-02-2014
    Posts
    15

    Re: Need help calculating age field but want blank if nothing is entered in DOB field

    Appreciated. Works great - thanks! Any ideas on my other post of trying to allow people to type paragraphs in Excel and be able to edit them later and keep the formatting of Word? I think so far my best option is Text Box...but we still have to resize if the paragraph is bigger than the box...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 01-16-2014, 03:33 AM
  2. Need field to remain blank unless data is entered.
    By ranchqueen in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-15-2013, 11:51 PM
  3. Replies: 0
    Last Post: 06-26-2012, 09:06 PM
  4. Replies: 3
    Last Post: 03-03-2012, 12:16 PM
  5. Unlocking a field once an entry field has been entered
    By jodders in forum Excel General
    Replies: 2
    Last Post: 03-31-2008, 07:52 AM

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