Age Calculation

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
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?
Photo of German

German

  • 0 Points

Posted 6 years ago

  • 0
  • 1
Photo of Mark_Shnier

Mark_Shnier

  • 700 Points 500 badge 2x thumb
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)

Photo of QuickBasePros (Laura Thacker)

QuickBasePros (Laura Thacker), Champion

  • 5,436 Points 5k badge 2x thumb
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
)
Photo of Paula

Paula

  • 0 Points
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!
Photo of QuickBasePros (Laura Thacker)

QuickBasePros (Laura Thacker), Champion

  • 5,436 Points 5k badge 2x thumb
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?
Photo of Paula

Paula

  • 0 Points
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)
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
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.  
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
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) 
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)
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
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. 
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)
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks a lot. Getting an error after changing the DOB to my field.
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)
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks.  Still  look the same as I am getting the same error. 
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)
Photo of Justin Parker

Justin Parker

  • 152 Points 100 badge 2x thumb
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!
Please post your formula
Photo of Justin Parker

Justin Parker

  • 152 Points 100 badge 2x thumb
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)
//
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]
(Edited)
Photo of Justin Parker

Justin Parker

  • 152 Points 100 badge 2x thumb
Sorry, I misunderstood.  My goal is to have a result to one decimal point.  So show 1.1 years as opposed to 1.0
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.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks a lot. Works great.