Display seconds always "00"

[Legacy 143009]

Hi,

TEXT(A1,"hh:mm:ss##:##:00") is not working. How can I format the time as it will show seconds always "00"?
I don't want to use any floor or round function because the problem I face is already because of precision overflow. So I want to take a shortcut with text formatting.

Eg: 01:00:01 => 01:00:00

Thanks a lot!

 

[JonXL]

If you never want seconds to show any value other than 00, why include them in the first place?

=TEXT(A1,"hh:mm")&":00"

 

[Tetra201]

Maybe hh:mm":00"

 

[Legacy 143009]

If you never want seconds to show any value other than 00, why include them in the first place?

=TEXT(A1,"hh:mm")&":00"

Well, I'll be ****ed.

 

[Rick Rothstein]

Hi,

TEXT(A1,"hh:mm:ss##:##:00") is not working. How can I format the time as it will show seconds always "00"?

Another way...

TEXT(A1,"hh:mm\:\0\0")

Putting a backslash in front of a character that the TEXT function (or Cell Formatting for that matter) uses for a special purpose takes that special purpose away and considers the character that follows the backslash to simply be that character.

 

[Legacy 143009]

Another way...

TEXT(A1,"hh:mm\:\0\0")

Putting a backslash in front of a character that the TEXT function (or Cell Formatting for that matter) uses for a special purpose takes that special purpose away and considers the character that follows the backslash to simply be that character.

A more elegant solution.