Forum Discussion

adrianoramos520's avatar
adrianoramos520
New Member
6 years ago
Solved

Converting string ex. "117:50:00" into hours

Well. I have two collums with a start service and other with the date closed as pic below:

 

I inserted a new collumn with the code:

(h):mm:ss =
var Hora= QUOTIENT('Incidentes (2)'[Segundos],3600)
var Minut0= QUOTIENT('Incidentes (2)'[Segundos]-'Incidentes (2)'[var Hora]*3600,60)
var Segundo='Incidentes (2)'[Segundos]-'Incidentes (2)'[var Hora]*3600-'Incidentes (2)'[var Minuto]*60
return
CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE('Incidentes (2)'[var Hora],":"),'Incidentes (2)'[var Minuto]),":"),'Incidentes (2)'[var Segundos])
 
And I got the result I wanted but as "String" and I'm not able to convert it into hours. So I can't find the time average and others calculations I expected.
When I try to convert it I get the message: "Its not possible convert the value '117:50:0' type of String into Date".
 
 
What am I doing wrong? Could you please help me?
Thanks a lot. 
Need Help

11 Replies

    • adrianoramos520's avatar
      adrianoramos520
      New Member
      6 years ago

      Thanks for everyone that tried to help me. v-eachen-msft its worked fine for me, and now I can perform some measures.

      Thank you so much.

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    6 years ago

    Have you tried this ?

     

    ([Resolvido] - [Criado em]) * 24

     

    • adrianoramos520's avatar
      adrianoramos520
      New Member
      6 years ago

      Yeah but I got a negative number.... and I still can't convert it into hours. Take a look.

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        6 years ago

        Can you show the power query code and some data samples in a table?

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    I see you are trying to solve this with a DAX column but why not do it in the query editor.  Do you need the hh:mm:ss format?  Or is that a step to get you toward total hours?  If the latter, you can get the total hours between the two columns with Duration.TotalHours([Resolved] - [Created]).  This will give total hours as a decimal number, which you can round to the nearest hour, if needed.

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat