How to calculate age from a DOB field in SharePoint

Question

Hi

I am trying to add a number column which calculates the age of an individual from a DOB column on an online SharePoint list.

I have tried using the below JSON codes but I'm only getting a return value of 54? Please can someone advise where I am going wrong?

{

"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",

"elmType": "div",

"txtContent": "=floor((Number(@now)-Number([$DOB]))/31557600000)"

}

and

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=floor((Number(@now)-Number([$DOB]))/(1000*60*60*24*12)/365*12)"
}

Answer 1

Hi @Samantha Winyard ,

Based on your description, I tested and there are a few cases where the calculated value only returns 54, please check that you don't have any of the following:

1.I suggest you check that you added JSON to the Age column. I accidentally added JSON to the date column and found that the return value was 54.

2.If you didn't fill in the date of your birthday first, you added JSON to the age column. Age will only return to 54. Not even if you add a date value later.

Second, if the column is okay, please try this JSON:

{
 "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
"elmType": "div",
"txtContent": "=floor((Number(@now)-Number([$DOB]))/(1000*60*60*24*365))"
}

Note: My DOB column is a date and time column that doesn't include time. Add JSON to the Age column.

This JSON works fine in my environment:

Third, if SharePoint JSON still doesn't work, we can try using calculated columns to show the age.

Column Settings > Columns > Create a new column:

Formula:

=DATEDIF([DOB], TODAY(), "Y")

It also returns the correct result:

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi! Here’s how you can use the formula in a calculated column in SharePoint:

Access your SharePoint list or library where you want to add the calculated column.

Add a new calculated column:

• Click on + Add column and select More.
  • Choose Calculated (calculation based on other columns).

1. Configure the calculated column:

• Give the column a name, such as “Age”.
• In the Formula field, enter the formula:

=FLOOR((Number([Today])-Number([Date of Birth]))/31557600000)

• Replace [Date of Birth] with the column that contains the date of birth.

Set the return type for the formula:

• Choose Number as the data type returned by the formula.

Save the column:

  - Click **OK** to save the new calculated column.
  

If there are any misunderstandings, please let me know

Best regards，

Maycon Novaes

If the Answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

The formula might be returning a value around 54 because of the way the dates are being interpreted or calculated. Here are a few things to check:

Date Format: Ensure that the date format for the “Date of Birth” column is correct and consistent. If the dates are not in the expected format, the calculation might be incorrect.

Current Date Column: If you are using a static date for the current date (e.g., a fixed date in the “Today” column), make sure it is updated to the current date. Ideally, you should use a dynamic method to get the current date.

Formula Syntax: Double-check the formula syntax to ensure there are no errors. The formula should look like this:

=FLOOR((Number([Today])-Number([Date of Birth]))/31557600000)

Data Type: Ensure that both the “Today” and “Date of Birth” columns are of the Date type. If they are not, the Number function might not convert them correctly.

Debugging: To debug, you can create additional calculated columns to see the intermediate values:

• A column to show the numeric value of the current date: =Number([Today])
  • A column to show the numeric value of the date of birth: =Number([Date of Birth])
    • A column to show the difference in milliseconds: =Number([Today])-Number([Date of Birth])

By checking these intermediate values, you can identify where the calculation might be going wrong.