Forum Discussion

dyoung's avatar
dyoung
Frequent Visitor
8 years ago
Solved

DataFormat.Error converting number to duration - am I missing something?

Hi there,

 

I have a column in my query editor which is a whole number. The source data is an amount of elapsed time in seconds.

 

I want this column to show days + hours, rather than seconds. My attempts so far have been:

 

Change Type>Duration.

 

But this produces a DataFormat.Error for any numbers above a certain threshold. eg., It can convert 10411192 but not 11842910.

 

Because of this my next two steps will only produce results for 8 digit numbers below a certain threshold.

 

These steps are: Duration>Divide value 86400

 

followed by: Transform>Total Days.

 

Is there something I'm missing on the above? Better yet, is there a smarter way to convert seconds to days + hours in the Query Editor?

 

Thanks in advance,


Dylan

 

 

 

 

  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi dyoung,

     

    Based on my test, you should be able to simply use the formula below to add a new custom to convert seconds to durations. :smileyhappy:

    = #duration(0,0,0,[Column1])

     

    Regards

2 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi dyoung,

     

    Based on my test, you should be able to simply use the formula below to add a new custom to convert seconds to durations. :smileyhappy:

    = #duration(0,0,0,[Column1])

     

    Regards

    • dyoung's avatar
      dyoung
      Frequent Visitor
      8 years ago

      Thanks so much for your reply, v-ljerr-msft.

       

      Have gone through my data and your formula works a treat.

       

      After my original post I'd come up with a different solution based off an old excel forumla I used:

       

      = [Column1]/86400

       

      Your forumla produces the exact same result which is great to know. I can sleep easy.

       

      Thanks again for helping out.