Forum Discussion

KirstyMacKinlay's avatar
KirstyMacKinlay
New Member
7 years ago
Solved

Format for reporting time spent

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

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

     

     

6 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    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's avatar
      KirstyMacKinlay
      New Member
      7 years ago

      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

  • Stachu's avatar
    Stachu
    Community Champion
    7 years ago

    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's avatar
      KirstyMacKinlay
      New Member
      7 years ago

      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's avatar
        Stachu
        Community Champion
        7 years ago

        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