0

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)

  • 1
    What did you already try? – Hennes Mar 1 '16 at 11:18
  • 1
    You might just need to set the format of the second cell to a date time format, but with concatenate involved it could be more complicated. You need better examples for this to be answerable. – Tyson Mar 1 '16 at 11:20
  • 3
  • I made a big change to your post, you can roll it back if you don't like it – Dave Mar 1 '16 at 12:25
  • Look at the duplicate question identified by Dave above. That is likely to be your answer. If not update your question with specific changes – Prasanna Mar 2 '16 at 3:22
2

Consider:

=CONCATENATE("Hi its ",TEXT(A1,"hh:mm:ss"))

enter image description here

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.