How to convert decimal time to hh:mm and summarize that column?

[Anonymous]

Hi,

I need to display for every person how long she worked each day and I need to display the summarized hours of all persons. It should be displayed in the format hh:mm.

Therefore I use a measure to convert the decimal time [IST] to minutes and another measure to convert from minutes to hh:mm.

minutes = TRUNC([IST])*60+(([IST])-Trunc([IST]))*60

hh:mm = [minutes]/60/24

It looks like this:

I don't really need to display the display the colums Ist and minutes.

I only need to display the column hh:mm and the total sum below.

How could I achieve that?

Many thanks for hints.

[speedramps · Accepted solution]

Hi Peterman

Don't use time units to diplay aggregated time, because they cant cope with more that 24hrs.

...

Then display results in visuals as text (eg display 65.5 mins  =  01:05:30)

I have taken trouble to help you, now please help me by giving kudos.
Please click the thumbs up if you like me trying to help you and click Accept as Solution if I have fixed you problem.

Thanks.

Total duration in MM:SS =

-- convert decimal duration to hh:mm:ss text  
-- eg converts  65.5 to  "01:05:30"   and converts 0 to  00:00

-- Take decimal minutes as input eg 5.5 minutes

VAR DecimalMinutes = AVERAGE(Calls[Conversation duration])

-- Convert decimal minutes to seconds

VAR Duration = DecimalMinutes * 60

-- Calc the days, hours, minutes and seconds  (add days if required

VAR Hours = INT(DIVIDE(Duration,3600,0))

VAR Minutes = INT(DIVIDE(MOD( Duration - ( Hours * 3600 ),3600),60,0))

VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0)

-- Format hours, minutes and seconds to two decimals

VAR H = IF (LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ))

VAR M = IF (LEN ( Minutes ) = 1, CONCATENATE ( "0", Minutes ), CONCATENATE ( "", Minutes ))

VAR S = IF (LEN ( Seconds ) = 1, CONCATENATE ( "0", Seconds ), CONCATENATE ( "", Seconds ))

RETURN

-- if input is 0 return 00:00.

-- return hours:mins:seconds as text

-- handles any errors(unlikely)

-- if input is greater than 24hrs then the hours will be displayed

IFERROR(

    IF (DecimalMinutes>0,

    H & ":" & M & ":" & S,

    "0:00"),

    "Error")

[watkinnc]

You could just do

hh:mm = TIME(0, [IST]*60, 0)

--Nate

[technolog]

For me it works nice. Thanks:)

[Anonymous]

Thank you, it works partially. I see the values as expected. How do I get the values summed up?

I could select Do not Summarize, Count or Count distinct from Default Summarization. There are no options to select any further items like Sum.

[CNENFRNL]