Calculating Age in SharePoint calculated field

In this post, we are going to discuss how to calculate age based on birthdate in SharePoint list field. Now you must be wondering, what is the big deal about this post? It is so easy to calculate age as calculated field.

Well, it’s not that easy. Try it out yourself. First you might think that what we can do is we can create one field with the name age and have it as a calculated field and write a formula which will calculate difference between today and the birthdate with using YEAR function and you are done. However this is not going to help. The reason behind this is we cannot use Today in the calculated field. If you try to do so, you will get an error. So below calculation will not work.



We cannot use Today in calculated field. This is the major drawback as of now. There are many instances where we need to calculate many things based on Today’s date. However when now it’s not possible directly, we need to play a trick here.

And the trick is to confuse SharePoint. Now how can we confuse SharePoint? Well answer is very simple. We all know that we can use fields in the calculated fields. It allows us to select the fields from the list which we already have like shown in above image.

So we are going to create a field called Today as single line of text. So go ahead and create a field called Today. Remember, that the name should be Today. Now create field called Age and then use the same formula.



So here what we have done is we have confused SharePoint. Because SharePoint thinks that you will use this as part of a field formula which we will not.

Now go ahead and delete the Today field that we’ve created. We are done. Enter the data, enter birthdate and see the Age field populating.



Do remember one thing. If you need by any chance to change the field settings of Age or any field that you may or may not encounter error again. In that case, you again need to create a field called Today. Work on your modifications and then delete Today column. It’s a trick. Well to my opinion, this can vary. Because when I tested this on one server, it gave me error when I clicked on Ok button of Age field after removing Today column. And on the other server, it went fine. So still not sure why is this.

However at least I have showed you how to calculate Age. Wasn’t that a nice trick that we played with SharePoint? Do let us know.