Format for reporting time spent

[KirstyMacKinlay]

Hi

I have a report pulled from a spreadsheet which lists time spent by users in a decimal format.  i.e. 0.08 = 5mins

This means that the time spent report displays in decimal, but this does not make reading the report easy.

Is there any way of converting the decimal duration into a format that shows in "x hours xx mins". i.e. 2.67 = 2h 40m

Thanks

[v-lili6-msft · Accepted solution]

hi, @KirstyMacKinlay

You could try this formula as below:

Measure = var h=INT([SUMTIME]/60) return
h&"h"&([SUMTIME]-h*60)&"minte"

or

Measure 2 =
VAR h =
    INT ( [SUMTIME] / 60 )
RETURN
    IF (
        ( [SUMTIME]
            - h * 60 )
            = 0,
        h & "h",
        h & "h"
            & ( [SUMTIME]
            - h * 60 )
            & "minte"
    )

Note: SUMTIME = CALCULATE(SUM(Table2[time spent]))

Result:

Best Regards,
Lin

[KirstyMacKinlay]

Hi Lin

Thanks, that looks like it should work, but I am finding that I get the same message as I got yesterday from the other suggestion which says "The value for 'SUMTIME' cannot be determines.  Either 'SUMTIME' Doesn't exist, or there is no current row for a column named 'SUMTIME'

However, I do have a column for SUMTIME.  I am not able to pick up any of my columns?

Thanks

Kirsty

[v-lili6-msft]

hi, @KirstyMacKinlay

SUMTIME is a measure, not a column.

SUMTIME = CALCULATE(SUM(Table2[time spent]))

You should learn difference between measure and column first.

here is a demo pbix, please try it.

Best Regards,
Lin

[Stachu]

you can use FORMAT with calculating number if minutes by multiplying the decimal value by 60:

Measure = FORMAT([Value],INT([Value]) & ":" & FORMAT(ROUND([Value]*60,0),"00"))

or something like this

Measure = FORMAT([Value],INT([Value]) & "h " & FORMAT(ROUND([Value]*60,0),"00m"))

[KirstyMacKinlay]

Thanks Stachu

This Measure didn't give me the caluclation that I was looking for.  I just got an error message.

I did manage to get the time converted to a number of minutes. 

However, my customer needs the matrix report to show the time as 5hr, not 300min

Or 6hr 56min, not 416min

Is there a way of formatting the matrix sum to show the time in this way, rather than in a single unit of time?

Thanks

[Stachu]

can you share the syntax you used in the end? 

the second measure I posted should work for you, as long as [Value] is the measure where the aggregations is happening and [Measure] is just used to present it differently