Concatenate cells with different formatting...

[RobbieC]

Hi there, I have 2 cells with values. One is formatted as 'general' (A1) and one with a 'custom' format - h"hrs" mm"m" (B1)

I need to concatenate the 2 together in (C1)

The value in (A1) is: "Time spent running: "
The Value in (B1) is: 0.0347, which after the formatting shows '0hrs 50m'

I was hoping to get a result in (C1) as: "Time spent running: 0hrs 50m"

but the actual result is "Time spent running: 0.0347"

I have found that if I change the formula in C1 to:

CONCATENATE(A1," - ",TEXT(B1, "h:mm"))

the result is "Time spent running: 0:50", but I really want "Time spent running: 0hrs 50m"...

Is there a way to combine my 2 cells, yet retain the formatting of my hours and mins?

If you can point me in the right direction, I'd be really grateful

Thanks

 

[Rick Rothstein]

Try it like this...

=CONCATENATE(A1," - ",TEXT(B1,"h""hrs"" mm""m"""))

 

[RobbieC]

As per usual Rick, you are the man! Thank you

 

[cerfani]

You actually are concatenating the value " - " inbetween so your output would include that. Not sure why you included it but did not mention it as part of the output result. Anyways you can do this...

="Time spent running: " & ROUNDDOWN(0.0347*24,0) & "hrs " & ROUND(0.0347*24-ROUNDDOWN(0.0347*24,0)*60,0) & "m"

which in your sheet would look like

=A1 & ROUNDDOWN(B1*24,0) & "hrs " & ROUND(B1*24-ROUNDDOWN(B1*24,0)*60,0) & "m"

i wrote those here so double check for typos