Skip to Content
Author's profile photo Saurabh Saxena

How to convert key figure type “Duration(s)” into a meaningful format

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.

/wp-content/uploads/2016/01/1_876763.jpg

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.

/wp-content/uploads/2016/01/2_876818.jpg 

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      This is a very useful blog.  Thank you.

      Author's profile photo Andrew Smith
      Andrew Smith

      Hello, how do you get comma's ''," into the formula when entering it?