How to Calculate Age in SharePoint List

Calculating age in a SharePoint list can be achieved using column formatting and view formatting. By utilizing these features, you can easily display the age of individuals stored in your SharePoint list.

To begin, you can create a calculated column in SharePoint and utilize JSON formatting to calculate the age in years. This can be done by using the formula =floor((Number(@now)-Number([Date of Birth]))/(1000*60*60*24*12)/365*12). This calculated column will display the age based on the provided date of birth.

However, it’s important to note that the calculated column will not automatically update as time passes. To ensure the age column remains accurate, there are a couple of options available. One option is to use a Flow, allowing you to automatically update the age column on a daily basis. Another alternative is to implement column and view formatting, where you can specify the age calculation in JSON and customize the display. This will update the age column every time the page or view is loaded.

In the SharePoint documentation, you can find more information and examples on how to use column formatting and view formatting. Feel free to explore and customize the formatting to display the age in a way that suits your needs, whether it’s in days, months, or years, by modifying the JSON code.

With column and view formatting capabilities, SharePoint provides a powerful solution for calculating and displaying age in your SharePoint list. By leveraging these features, you can easily keep track of age-related data in a dynamic and efficient manner.

Using Column Formatting and JSON

One approach to calculate age in a SharePoint list is by creating a calculated column and using JSON. To do this, you can follow these steps:

  1. Create a new column in your SharePoint list and choose the “Calculated” column type.
  2. In the formula field, enter “=floor((Number(@now)-Number([Date of Birth]))/(1000*60*60*24*12)/365*12)”. This formula calculates the age in years.
  3. Save the column and navigate to the list view where you want to display the age.
  4. Click on the “Format current view” option and choose “Format columns”.
  5. In the JSON formatting pane, specify the desired format for displaying the age.
  6. For example, you can use the following code to display the age in years:
{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
  "additionalRowClass": "=if(Number([$Age]) < 18, 'minor', '')",
  "attributes": {
    "class": "=if(Number([$Age]) < 18, 'minor', '')"
  },
  "children": [
    {
      "elmType": "div",
      "txtContent": "[$Age]",
      "style": {
        "font-weight": "bold",
        "color": "=if(Number([$Age]) < 18, 'red', 'black')"
      }
    }
  ]
}

This JSON code will format the age column by applying a CSS class to differentiate minors, and it will display the age in bold with red color for minors and black for adults.

Summary

In conclusion, column formatting and JSON can be utilized to calculate and display age in a SharePoint list. By creating a calculated column with a specific formula and applying JSON formatting to the view, you can customize the display of the age and even add additional styling or class modifications. Remember, this approach requires manually updating the age column or using a Flow to automate the process. Consult the SharePoint documentation for more examples and information on customizing column and view formatting.

Column Data Type Description
Date of Birth Date/Time The column where the date of birth is stored.
Age Calculated The calculated column that displays the age.

Updating the Age Column

To ensure the age column is always up to date, you can use a Flow or view formatting to automate the updating process.

To update the age column with a Flow, you can create a scheduled Flow that runs daily. This Flow can retrieve the current date and calculate the age based on the date of birth stored in the SharePoint list. The calculated age can then be automatically updated in the age column. By setting up this Flow, you can keep the age information accurate and eliminate the need for manual updates.

Alternatively, you can use view formatting to dynamically update the age column. With view formatting, you can specify the age calculation in JSON and customize the display. When the page or view is loaded, the age column will be automatically updated with the calculated age. This provides a seamless and efficient way to ensure the age information is always current.

By leveraging either Flow or view formatting, you can automate the updating process of the age column in your SharePoint list. This saves time and ensures accuracy, allowing you to focus on other important tasks. For more detailed instructions and examples on how to use these methods, refer to the SharePoint documentation.

Meet the Author

Abdul Rahim has been working in Information Technology for over two decades. Learn how Abdul got his start as a Tech Blogger , and why he decided to start this Software blog. If you want to send Abdul a quick message, then visit his contact page here.