Discussions

 View Only
Expand all | Collapse all

Age Calculation

Arshad Khwaja

Arshad Khwaja05-10-2017 21:27

  • 1.  Age Calculation

    Posted 07-21-2013 21:59
    I have a Date of Report field, a Date of Birth field and next to it we compute Age; how can I fill the Age field automatically based on DOB and the original date of the report?


  • 2.  RE: Age Calculation

    Posted 07-21-2013 23:10
    this was posted about a year ago by another user (ddixonr). It looks right to me.

    I'm assuming here that your field for date of report is called [DOR]
    The field type would be formula-numeric

    Year([DOR])-Year([DOB])-1
    +
    If(Month([DOR])>Month([DOB]),1,0)
    +
    If(Month([DOR])=Month([DOB]) and Day([DOR])>=Day([DOB]),1,0)



  • 3.  RE: Age Calculation

    Posted 07-22-2013 19:04
    I answered a similar post the other day. Here's the formula I use. Here you would substitute the [Start Date] for a [Date of Birth] field.

    var number currMonth=Month(Today());
    var number currDay=Day(Today());
    var number startMonth=Month([Start Date]);
    var number startDay=Day([Start Date]);

    Case(true,
    $currMonth = $startMonth and $startDay <= $currDay, (ToDays(Today()-[Start Date])/365),
    $currMonth = $startMonth and $startDay > $currDay, (ToDays(Today()-[Start Date])-365)/365,
    $startMonth < $currMonth,Floor(ToDays(Today()-[Start Date])/365),
    $startMonth > $currMonth,(ToDays(Today()-[Start Date])-365)/365
    )


  • 4.  RE: Age Calculation

    Posted 02-05-2014 17:57
    I use above formula for age calculation. The age is corrected only if DOB month is Jan - Sept, it will be one year less for DOB month Oct-Dec. I am unable to solve the issue here. Help needed!


  • 5.  RE: Age Calculation

    Posted 02-05-2014 21:25
    So you are asking for the formula to be modified so that if the individual was born between October 1 and December 31; then you want their age to calculate 1 year less than their actual age?


  • 6.  RE: Age Calculation

    Posted 02-05-2014 22:07
    You are right. I couldn't figure out how to modify.

    Below formula works.

    (Year(Today())-Year([DOB])-1)+
    If(Month(Today())>Month([DOB]),1,0)+
    If(Month(Today())=Month([DOB]) and Day(Today())>=Day([DOB]),1,0)


  • 7.  RE: Age Calculation

    Posted 04-21-2017 04:29
    Any possibility if I could have a formula that reports year and number of months such as 3.6 (3 years and 6 months). If this is not possible if I could have a numeric age as 3.5.  


  • 8.  RE: Age Calculation

    Posted 05-09-2017 23:32
    I am calculating the age of a store using today's date and the latest dev date. As I am also looking up dev dates for future too, my age is also being calculated as negative. In the following formula fow do I restrict the result to count latest dev date on or before today? 



    var date LDD = [Latest Dev Date];
    var date PolicyDate = Today();


    Year($PolicyDate) - Year($LDD)
    -
    If(
    Month($PolicyDate) < Month($LDD)
    or
    (Month($PolicyDate) = Month($LDD) and Day($PolicyDate) < Day($LDD)),1,0) 


  • 9.  RE: Age Calculation

    Posted 05-10-2017 02:38
    Try this

    var date LDD = [Latest Dev Date];
    var date PolicyDate = Today();

    var Number Age =
    Year($PolicyDate) - Year($LDD)
    -
    If(
    Month($PolicyDate) < Month($LDD)
    or
    (Month($PolicyDate) = Month($LDD) and Day($PolicyDate) < Day($LDD)),1,0);

    Max(0, $Age)


  • 10.  RE: Age Calculation

    Posted 05-10-2017 02:51
    Very grateful.  Works great. Any chance we could show a blank instead of a zero and whether the calculation could be more precise than rounded number, does not matter if it is 3.5 for 3 years and 6 months. 


  • 11.  RE: Age Calculation

    Posted 05-10-2017 03:19
    Here is a formula to calculate age in decimal years. It will be blank if negative.

    AGE and decimal months

    // replace the [DOB] field with your date of birth field

    var date DOB = [DOB];

    var number Years =

    Year(Today())-Year($DOB)
    -
    If(
    Month(Today()) 0, $MonthsDifference, 12 + $MonthsDifference);

    Var Number Age = Round($Years + $Months/12,0.1)

    If($Age >= 0, $Age)


  • 12.  RE: Age Calculation

    Posted 05-10-2017 03:34
    Thanks a lot. Getting an error after changing the DOB to my field.


  • 13.  RE: Age Calculation

    Posted 05-10-2017 03:47
    I think somehow I lost part of the formula when I pasted it in.

    AGE and decimal months

    // replace the [DOB] field with your date of birth field

    var date DOB = [DOB];

    var number Years =

    Year(Today())-Year($DOB)
    -
    If(
    Month(Today()) 0, $MonthsDifference, 12 + $MonthsDifference);

    var number Age = Round($Years + $Months/12,0.1)

    If($Age >= 0, $Age)


  • 14.  RE: Age Calculation

    Posted 05-10-2017 03:50
    Thanks.  Still  look the same as I am getting the same error. 


  • 15.  RE: Age Calculation

    Posted 05-10-2017 12:36
    Sorry, I think that something weird was happening when I was copying and pasting from my iPad.  I am back on my regular computer now.

    //AGE and decimal months

    // replace the [DOB] field with your date of birth field

    var date DOB = [DOB];

    var  number  Years = 

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

    var number MonthsDifference = Month(Today()) - Month($DOB) ;
    var number Months = If($MonthsDifference > 0, $MonthsDifference, 12 + $MonthsDifference);

    Round($Years + $Months/12,0.1)


  • 16.  RE: Age Calculation

    Posted 03-21-2018 16:44
    Hello,

    I am using this formula field to calculate years with the company in my QB App and Im getting the following bug.

    The bug occurs when the employee start month/day is after the same month/day for that month.  It fixes itself after the month changes.  Here are 2 examples.

    1. Employee Joe Smith is hired on March 3rd 2018.  His "years with company" field will display 1.0 until April 1st when it will revert to 0.1
    2. Employee Jane Doe will be with the company for 1 year on March 20th 2018. On March 20th her "years with the company" will display 1.0.  On March 21st her "years with the company field" will switch to 2.0 until April 1st well it will revert to 1.1

    Any help would be great!


  • 17.  RE: Age Calculation

    Posted 03-21-2018 16:54
    Please post your formula


  • 18.  RE: Age Calculation

    Posted 03-21-2018 16:55
    Here you go!
    //
    var date DOB = [Date of Hire];



    var  number  Years = 


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


    var number MonthsDifference = Month(Today()) - Month($DOB) ;
    var number Months = If($MonthsDifference > 0, $MonthsDifference, 12 + $MonthsDifference);


    Round($Years + $Months/12,0.1)
    //


  • 19.  RE: Age Calculation

    Posted 03-21-2018 17:01
    Is your goal to have an integer result or a value with decimals.

    Here is a formula for an integer result

    var date DOB = [DOB];

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

    Edit:  In the first line put in your date field where it has [DOB]


  • 20.  RE: Age Calculation

    Posted 03-21-2018 17:55
    Sorry, I misunderstood.  My goal is to have a result to one decimal point.  So show 1.1 years as opposed to 1.0


  • 21.  RE: Age Calculation

    Posted 03-22-2018 14:00
    I don't have time to test right now, but can you test this formual for us.

    /AGE and decimal months

    // replace the [DOB] field with your date of birth field

    var date DOB = [DOB];

    var  number  Years = 

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

    var number MonthsDifference = Month(Today()) - Month($DOB) ;
    var number Months = If($MonthsDifference >= 0, $MonthsDifference, 12 + $MonthsDifference);

    Round($Years + $Months/12,0.1)


    I made a slight change on the bolded area.


  • 22.  RE: Age Calculation

    Posted 05-10-2017 21:27
    Thanks a lot. Works great. 


  • 23.  RE: Age Calculation

    Posted 03-16-2023 23:58

    #Formulasandfunctions 



    ------------------------------
    Justin Torrence
    Quickbase Expert, Jaybird Technologies
    jtorrence@jaybirdtechnologies.com
    https://www.jaybirdtechnologies.com/#community-post
    ------------------------------