Calculate Tenure in Months using calculated column in ORD (Report – Canvas)

We come across various scenarios while reporting when we need to calculate the tenure in terms of years, months or days. In this blog post, we will discuss how to calculate the tenure in terms of months using calculated columns in ORD.

NOTE: The condition applied here is if the starting/ ending date is mid of a month, then that month is also calculated as 1 whole month.

Let’s start the explanation with an example.

Let us consider that the starting date be Date1 = 15/5/1971 and ending date be Date2= 15/4/1995 in our case. We will calculate here the number of months between these two dates to find out the tenure in months.

First we will calculate the month number of the dates by applying ‘Month‘ function on Date1 and Date2. The two calculated columns are Month1 and Month2.

In our case, the values are:

Month1 >> Month func on Date1 >> 5
Month2 >> Month func on Date2 >> 4

Next, we will calculate the year number of the dates by applying ‘Year‘ function on Date1 and Date2.
The two calculated columns are Year1 and Year2.

In our case, the values are:

Year1 >> Year func on Date1 >> 1971
Year2 >> Year func on Date2 >> 1995

Now, we will calculate the number of months in the starting year using the calculated column: MonthCal1

In our example case, this calculated column calculates the months of the year 1971 including April i.e. 8

Next, we will calculate the number of months in the mid years using the calculated column: MonthCal2

In our example case, this calculated column calculates the total number of months in the years : 1972 to 1994 , both included i.e. 23*12=276

Finally we calculate the Tenure in Months by adding the months in the starting year, mid years and the final year in the calculated column: Tenure in Months

Final tenure in our example case is : 8 + 276 + 4 = 288

The final calculation will look like as follows:

In this way, we can achieve the requirement of calculating tenure in months using calculated columns in ORD.

Important pointer : There is another approach: calculating the age of both the dates using the “age” function in calculated column and taking a difference to find the number of years between the two dates. Then, dividing the resultant year by 12 to get the number of months between the dates. But in this approach, the number of months calculation in the starting year and ending year will go wrong as the age calculation only takes care of the years and not months, thus causing discrepancy in the final calculation.

Note: All the screenshots used in this blog are taken from Demo instance.