This blog explains how you can convert a key figure which defines a duration in seconds into more meaningful format. This comes handy when the duration measure goes into a big figure and it becomes inconvenient for an end user to interpret.

The assumption here is that you have a key figure which gives you duration in seconds or in some other unit, i.e. minutes, hours etc. The approach I am explaining here will break this time period into two different key figures. The first key figure will define the number of days in that duration and the second key figure will define the hours/minutes/seconds of the remaining period of that duration (after deducting the days out of it). An example below further explains the approach.

To create custom key figure 1, the formula would be = DIV(NODIM(“original duration key figure”),86400)

NODIM is used in above formula as DIV only work on numbers and the duration key figure includes a unit. Therefore to perform DIV, we need to remove the unit from the duration key figure.

To create custom key figure 2, the formula would be = TIME(“original duration key figure”)

TIME function works only on the remainder duration after deducting the whole days out. This is the reason that we need not use MOD in the formula calculation.

The below picture shows the above two custom key figures along with the original duration key figure to provide a better understanding of how the final outcome would look like.

Former MemberThis is a very useful blog. Thank you.

Andrew SmithHello, how do you get comma’s ”,” into the formula when entering it?