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,416 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,416 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)
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.