excel time serial back to text

Question

I have a time field of this format HH:MM:SS in many excel cells (when i import in as a csv). i would like to Attached the date field from another worksheet in and when I tried to concatenate =U10&" "&V10, let's said

U10 is 22-Dec
V10 is 18:14:01

will become

40169 0.759733796296296

so how can i convert a number like 0.759733796296296 back into 18:14:01 ?

Answer 1

Try changing the format of the cell back to DATE/TIME.

Note that 18:14:01 (18 hours, 14 minutes and 1 second) is about 75.97% of a 24 hour day.

Answer 2

Is your date field recognized as date?

I tried the following (actually adding date and time, not concatenating) and it worked:

22.12.2010 | 18:14:01

=A1+B1 => 22.12.2010 18:14:01

If you want the result as text only (not date), then you can format your time-field as Text (not Time), and concatenating should work the way you describe it.