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

[dyoung]

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 · Accepted solution]

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. 

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

Regards

[dyoung]

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.