2

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)

Dave
  • 25,297
  • 10
  • 57
  • 69
Ria
  • 21
  • 1
  • 1
  • 2
  • 1
    What did you already try? – Hennes Mar 01 '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 01 '16 at 11:20
  • 3
    Possible duplicate of [How to concatenate a date in MS-excel and receive a date (not a number)](http://superuser.com/questions/670423/how-to-concatenate-a-date-in-ms-excel-and-receive-a-date-not-a-number) – Dave Mar 01 '16 at 11:52
  • I made a big change to your post, you can roll it back if you don't like it – Dave Mar 01 '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 02 '16 at 03:22

2 Answers2

4

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.

Tyson
  • 1,490
  • 12
  • 13
3

Consider:

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

enter image description here

Gary's Student
  • 19,266
  • 6
  • 25
  • 39