Convert time to text

[PaulS]

Again a simple time problem, but by some reason I cannot get the right result.
Cell P9 holds a (start)time “10:45”, and cell P10 has the (end)time “11:00”. Both cells with time property and the result of a calculation with time values.
I want the value of both cells in a piece of text on another sheet.
For test purpose I tried to get the result “10:45 – 11:00” in a cell e.g. Cell T10.
I tried in T10: =TEXT(P9;”hh:mm”)&” - “&TEXT(P10;”hh:mm”).
This however returns “hh:01 – hh:01” . I have read all help on the time subject, but have not found the solution.
Can anyone help? Thanks.
Paul

 

[Richard Schollar]

Hi Paul

That's how I would do it. If you select any empty cell and right-click>Format Cells and format it as a Time format, if you then open up the VBE (Alt+F11) and then open the Immediate Window (Ctrl+G) and type in the following:

?Activecell.NumberFormat

what does it return?

 

[PaulS]

Hi Richard, when assigning the cellformat 'Time' the layout '13:30' was selected.
Following your suggestion in VBE returned 'h:mm;@' !! This is obviously not what I had in mind!
How do I correct that?
Paul

 

[prabby25101981]

Does this help ? -

=CONCATENATE(HOUR(P9),":",TEXT(MINUTE(P9),"00")," - ",HOUR(P10),":",TEXT(MINUTE(P10),"00"))

 

[PaulS]

The last solution works, thank you. But I also found my problem!
I use an English version of Excel, but in the Time properties of the cell I selected "dutch" as language! After changing the TEXT statement to TEXT(P9;”uu:mm”), the result was what I expected! Now I changed everything to 'English'. I hope that translation is automatically done when I send the XLS file to my collegue who has a Dutch version of Excel.....
Thanks to both of you. Your suggestions helped me to look carefully to all definitions.
Paul