Forum Discussion

Forecaster's avatar
Forecaster
Regular Visitor
4 years ago
Solved

Average Time Not Showing Correctly

Hi everyone 

 

I have a duration column which shows seconds and is a whole number.

 

I can get the average duration by using the formula:

 

avg = AVERAGE(Query1[Duration])

 

 

this gives me an aveage duration of 6570 seconds which is correct.

What I want to do is to convert this to HH:MM:SS

so I tried the following:

 

AvgTime = FORMAT(AVERAGE(Query1[Duration]),"HH:MM:SS")

 

This gives me a time of 00:23:02 which is not correct if you convert 6570 seconds to hours and mins.

 

What am I missing? 

 

Thanks 

Need Help
  • Forecaster's avatar
    Forecaster
    4 years ago

    I managed to solve this by doing the following:

    HHMMSS = 
INT(Query1[avg] / 3600) & ":" &
RIGHT("0" & INT(([avg] - INT([avg] / 3600) * 3600) / 60), 2) & ":" &
RIGHT("0" & MOD([avg], 3600), 2)

3 Replies