How to convert time in MM:SS

[FJ83]

Hi,

The whole data is based on seconds, Still, I would like to convert the data to MM: SS. Below was the formula I used but the calculation is working and unfortunately, the data was not validating correctly.

Response time in minutes = VAR duration = AVERAGE(CADD[Enter_to_Arrv_Seconds])

VAR Hours = INT ( Duration / 3600)

VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)

VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0)

RETURN

(Minutes*100+ Seconds)+0

Below is the column in the table

Kindly do the needful please !

[SanketBhagwat]

Hi @FJ83 
PLease try 'FORMAT" Dax function.
Refer below link;
https://docs.microsoft.com/en-us/dax/format-function-dax


Thanks,
Sanket
If this post helps, then mark it as 'Accept as Solution' and give it a thumbs up.

[FJ83]

Previously we used this calculation and then format the measure at 00:00, Currently, they redefine the columns by enter_to_arrv_seconds (Data type - Whole number). 

The result should be in this format: 03:40

Hold time in Mins = VAR Duration = AVERAGEX(

DAC,DATEDIFF(DAC[IDDate],DAC[DispatchedTime],SECOND))

VAR Hours = INT ( Duration / 3600)

VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)

VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0)

RETURN

(Minutes*100+ Seconds)+0

[v-easonf-msft · Accepted solution]

Hi， @FJ83 

In measure you cannot directly convert data to MM:SS.

You might consider using  character ‘&’ to concatenate characters to get the final result

Please modify your original formula as below:

Response time in minutes =
VAR _duration =
    AVERAGE ( CADD[Enter_to_Arrv_Seconds] )
VAR Hours =
    INT ( _duration / 3600 )
VAR Minutes =
    INT ( MOD ( _duration - ( Hours * 3600 ), 3600 ) / 60 )
VAR Seconds =
    ROUNDUP ( MOD ( MOD ( _duration - ( Hours * 3600 ), 3600 ), 60 ), 0 )
RETURN
    Hours * 60 + Minutes & ":" & Seconds

Best Regards,
Community Support Team _ Eason

[FJ83]

I'm looking the data validation side by doing the calculation based on the column i mentioned above,

[amitchandak]

@FJ83 , Hope it is number to time

New column = Quotient([Time in Second],60)  & ":" & MOD([Time in Second],60)  

or

TIme(Quotient([Time in Second],3600)  ,Quotient(Mod([Time in Second],3600) ,60), Mod(Mod([Time in Second],3600) ,60) )

[FJ83]

Hi @amitchandak 

How to find the average from the above calculation ?