Forum Discussion

ArchiveUser's avatar
ArchiveUser
Qrew Captain
8 years ago

How do I include or exclude a person within a date range with just a person's birth date and today date?

 I need to set this formula up over six potential date ranges that a person's record in the database could fall into. The only null condition I can think of is if a data entry person forgets to put the birthdate in the persons record during the intake process.
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    8 years ago

    Here's the ranges:

    0-11

    12-15

    16-24

    25-34

    35-44

    45-54

    55 and over. That's a total of seven age categories. I missed the last one in the original count.

  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    Try this are a formula text field 

    var date DOB = [DOB]; // put your date of birth field here

    var number AGE =
    Year(Today())-Year($DOB)
    -
    If(
    Month(Today())<Month($DOB)
    or
    (Month(Today())=Month($DOB) and Day(Today())<Day($DOB)),1,0);


    IF(
    isNull($DOB), "  Missing D.O.B.",
    $AGE <= 11, " 0-11",
    $AGE <= 15, "12-15,

    etc

    $AGE >=55, "55+")
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    8 years ago
    OK, I will give it a shot and let you know how it worked. Thanks so much. I would have never figured this out on my own!
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    8 years ago
    OK, it works! I never used a variable before. I think I learned something today, hopefully. Thanks for your help! I would have never of been able to get this without direction!
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    Formula variables are wonderful ways to make formulas more readable and easier to maintain.
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    8 years ago
    Yes, thanks to your input I just learned a valuable tool that I will try to apply as I chunk forward on my development. Again, thanks so much for your help.
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    8 years ago

    I am getting the formula to work somewhat. when I test it, I am getting incorrect results. I have tested the formula so far by:

    • Removing a date in the 'Date of Birth' to see if it returns the 'null' condition, "Missing D.O.B." which it does not.
    • There also seems to be something wrong with the age range '0-11' as it returns records that are over 11 years of age.


    Any suggestions on how to fix? If making the field return just a '1' if the record is within the age range or a null or 0 if the record is in the age range is easier, that would be great as I need to just count those records in each age range anyhow.

  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    8 years ago

    Forgot to give you the formula I used in the 0-11 age range field:


    var date DOB = [Date of Birth];
    var number AGE =
    Year(Today())-Year($DOB)
    -
    If(Month(Today())<Month($DOB)
    or
    (Month(Today())=Month($DOB) and Day(Today())<Day($DOB)),1,0);
    If(IsNull($DOB), "Missing D.O.B.",$AGE <=11, "0-11")

  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    I'm not understanding why you would want to have separate fields for each date range result. It should just be one field.

    When I get a chance tonight I will replicate the formula and test it, but it sure looks right to me.