Excel – Keeping time format in a concatenate

microsoft excelworksheet-function

I am working on a spreadsheet that shows durations in Column C2 as hh:mm:ss. I need to transfer this to Column N2 in mm:ss. Column N2 is part of a CONCATENATE in Column E2. When I put time in Column N2 it changes into decimal on the CONCATENATE in Column E2. How can I keep it in time format in the CONCATENATE with or without the colons?

Please consider

    A                 B
1  10:15:03           Hi it's 0.427453703703704

Cell A1 has the value as shown and is formatted as Time
B1 has the following

=CONCATENATE("Hi it's ", A1)

Best Answer

this should do it:

=CONCATENATE("Hi it's ", TEXT(A1,"HH:MM:ss"))

Use the TEXT function to change the time to human readable, rather than Excels decimal notation.

Related Question