Formatting Hours and Minutes

[Anthony Slater]

Hi

I'm using the simple formula =a1/(24*60) to convert my seconds to minutes.
Then I'm formatting the cell to Time (xx:xx:xx)

However, as my seconds are very large, the time looks a bit confusing. ie:-

9065378 seconds converts to 151089:38:00

How can I convert the seconds to minutes but also include a comma as a
seperator?

Or any other suggestions?

 

[JE McGimpsey]

One way:

=TEXT(INT(A1/60),"#,##0:")&RIGHT(TEXT(A1/1440,"hh:mm"),2)

 

[Anthony Slater]

Thanks M8

That worked a treat !!

One way:

=TEXT(INT(A1/60),"#,##0:")&RIGHT(TEXT(A1/1440,"hh:mm"),2)