Second() format :05 not :5

[guybrown]

Hi

The following returns 12:34:6 (note 6 not 06)
=CONCATENATE(HOUR(A1),":",MINUTE(A1),":",SECOND(A1))

Any ideas on how I can get 06?

Cheers,

Guy

It's ok, I've got it thanks all!

=HOUR(A1)&":"&MINUTE(A1)&":"&IF(LEN(SECOND(A1))=1,"0"&SECOND(A1),SECOND(A1))

 

[CWatts]

To answer your question specifically (this will also work for the MINUTE section as well):

TEXT(SECOND(A1),"00")

I'm guessing your cell A1 has time AND date in it. You can use =A1-INT(A1) to extract just the time portion and then format with normal cell formatting unless of course you want it as text.

 

[mikerickson]

Why not use the TEST function in place of the whole formula

=TEXT(A1,"h:m:ss")

(although if you want 2:09:34 instead of 2:9:34 "h:mm:ss" should be used.)

 

[guybrown]

Thanks all. I appreciate your repsonses.

The TEXT function is a lot simpler and covers minutes as well. Cheers.