How can I convert date of birth into age as of a certain date in JMP?

[#1]

How can I convert date of birth into age as of a certain date in JMP?

[#2]

JMP stores dates as seconds since a reference date.  So if your values are of date type, you can subtract them to determine age.  To convert to days, divide by 3600/24/365.25.  Here's a simple example that shows how to do this using a formula in a table:

New Table( "Age Calculation",
      Add Rows( 2 ),
      New Column( "DOB", Numeric, Continuous, Format( "ddMonyyyy", 10 ),
            Input Format( "ddMonyyyy" ), Set Values( [3029529600, 3197750400] )
      ),
      New Column( "Reference Date", Numeric, Continuous, Format( "ddMonyyyy", 10 ),
            Input Format( "ddMonyyyy" ), Set Values( [3507494400, 3507494400] )
      ),
      New Column( "Age", Numeric, Continuous, Format( "Fixed Dec", 12, 2 ),
            Formula( (((:Reference Date - :DOB) / 3600) / 24) / 365.25 )
      )
);

[#3]

Thank you. I've tried something similar to that in the original file, which was in MS Excel. However, for persons < 1 year of age, the conversion gave me negative numbers.

[#4 · Accepted solution]

The Date Difference() function is another alternative.

The attached data table has an example.

[#5]

Don't forget that there are quite a few date and time functions built into JMP for formulas and scripts. I like them because they are convenient, easy to remember, and the purpose is clear. For example, if I wanted to know the difference in dates as biweekly intervals, I would divide the difference in seconds by In Weeks(2). If I wanted to know the elapsed time in days, I would divide by In Days(1).

I know that it is easy to compute the number of seconds yourself, but I prefer to use these helper functions.

Go to Help and select Scripting Index. Change the focus to Functions and select the Date Time group. You can see them all at once. You can select one of them and test it on the right or click Help to go to the full documentation.

[#6]

I create my own formula in JMP.  For example, if I want to get the member age on the date of the application, I subtract the date of birth from the application date.  Then, divide by 86400 and 365 to get the figure in years.  See below.

newcolumn("App Date - Birth Date",numeric, formula(
 
((:App Date - :Birth Dt) / 86400) / 365));

Once I have the member age, I group this figure by applying the formula below:

newcolumn("Member Age Range",numeric, formula(
 
If(
 
17 < :Name( "App Date - Birth Date" ) <= 25, "18-25",
 
25 < :Name( "App Date - Birth Date" ) <= 35, "26-35",
 
35 < :Name( "App Date - Birth Date" ) <= 45, "36-45",
 
45 < :Name( "App Date - Birth Date" ) <= 55, "46-55",
 
55 < :Name( "App Date - Birth Date" ) <= 65, "56-65",
 
65 < :Name( "App Date - Birth Date" ) <= 75, "66-75",
 
"76+"
 
)));