# Discussions

View Only

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

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

Posted 05-24-2017 16:13
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.

• #### 2.  RE: How do I include or exclude a person within a date range with just a person's birth date and today date?

Posted 05-24-2017 16:17
Can you say in words what the 6 ranges are?

• #### 3.  RE: How do I include or exclude a person within a date range with just a person's birth date and today date?

Posted 05-24-2017 16:20

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.

• #### 4.  RE: How do I include or exclude a person within a date range with just a person's birth date and today date?

Posted 05-24-2017 17:00
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+")

• #### 5.  RE: How do I include or exclude a person within a date range with just a person's birth date and today date?

Posted 05-24-2017 17:07
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!

• #### 6.  RE: How do I include or exclude a person within a date range with just a person's birth date and today date?

Posted 05-24-2017 17:23
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!

• #### 7.  RE: How do I include or exclude a person within a date range with just a person's birth date and today date?

Posted 05-24-2017 17:26
Formula variables are wonderful ways to make formulas more readable and easier to maintain.

• #### 8.  RE: How do I include or exclude a person within a date range with just a person's birth date and today date?

Posted 05-24-2017 17:29
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.

• #### 9.  RE: How do I include or exclude a person within a date range with just a person's birth date and today date?

Posted 05-24-2017 18:09

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.

• #### 10.  RE: How do I include or exclude a person within a date range with just a person's birth date and today date?

Posted 05-24-2017 18:11

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")

• #### 11.  RE: How do I include or exclude a person within a date range with just a person's birth date and today date?

Posted 05-24-2017 19:25
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.

• #### 12.  RE: How do I include or exclude a person within a date range with just a person's birth date and today date?

Posted 05-24-2017 19:49
I have attached a jpg of the data requested in a monthly report that segregates the age categories needed for reporting. Maybe I am approaching this problem the wrong way??? Should this be done through the QB reports process?

• #### 13.  RE: How do I include or exclude a person within a date range with just a person's birth date and today date?

Posted 05-24-2017 20:47
OK, I f you want to easily create a report to look like that, yes I see why you would want to have separate fields.  You would want that to be a numeric formula field to calculate to a 1 or a 0.

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), 0, \$AGE <=11, 1,0)

I did test the formula I originally posted and it worked for me.  Contact me via the information in my profile if you would like some one on one assistance.

• #### 14.  RE: How do I include or exclude a person within a date range with just a person's birth date and today date?

Posted 05-25-2017 12:40
I think I am getting it. Just a question on the ranges; For the next age range, '12-15' is the last formula this:     If(IsNull(\$DOB), 0, \$AGE >11<=15, 1,0)

• #### 15.  RE: How do I include or exclude a person within a date range with just a person's birth date and today date?

Posted 05-25-2017 12:45
You were close

If(IsNull(\$DOB), 0,
\$AGE >11 and \$AGE<=15, 1,0)

• #### 16.  RE: How do I include or exclude a person within a date range with just a person's birth date and today date?

Posted 05-25-2017 14:41
Thanks. I have added the fields based on each age category and tested it with various DOB's. It looks good. It's working! Thanks so much for your help. I am not sure I would have been able to get this one on my own.

• #### 17.  RE: How do I include or exclude a person within a date range with just a person's birth date and today date?

Posted 05-25-2017 14:50
Great to hear.  It gets easier the more you play with the product!