# Age Calculation

• 0
• Question
• Updated 1 year ago
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?
• 0 Points

Posted 6 years ago

• 0
• 700 Points
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)

QuickBasePros (Laura Thacker), Champion

• 5,436 Points
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
)
• 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!

QuickBasePros (Laura Thacker), Champion

• 5,436 Points
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?
• 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)
• 3,152 Points
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.
• 3,152 Points
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)
• 72,448 Points
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)
• 3,152 Points
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.
• 72,448 Points
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)
• 3,152 Points
Thanks a lot. Getting an error after changing the DOB to my field.
• 72,448 Points
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)
• 3,152 Points
Thanks.  Still  look the same as I am getting the same error.
• 72,448 Points
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)
• 152 Points
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!
• 72,448 Points
• 152 Points
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)
//
• 72,448 Points
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)
• 152 Points
Sorry, I misunderstood.  My goal is to have a result to one decimal point.  So show 1.1 years as opposed to 1.0
• 72,448 Points
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.
• 3,152 Points
Thanks a lot. Works great.