# Concatenate & show HH:mm - as a time format

#### Smokeyham

##### Board Regular
Hello,

I have a spreadsheet which calculates the median time. I want to concatenate this with some text.

The desired end result might read "Median time is 00:15" - meaning 0 hours and 15 minutes.

I am using the following formula
=Analysis!A2&" "&Analysis!B2&" minutes"

Where......
Analysis!A2 contains the text "Median time is"
Analysis!B2 contains the calculated value "00:15"

The problem I run across is that the concatenated result of my formula =Analysis!A2&" "&Analysis!B2&" minutes" is the following:

Median time is 0.014659870321 minutes

In the calculated cell (Analysis!B2) the value shows up just fine (00:15). The problem I have is when I concatenate this.

I've tried using a text function "&text(Analysis!B2,00:00) but I get an error message.

Any help would be appreciated.

Thanks.

Gordon

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Smitty

##### Legend
Looks like you're missing some apostrophes and have the wrong format:

=Analysis!A2&TEXT(Analysis!B2," hh:mm")

Hope that helps,

#### lenze

##### Legend
Code:
``=Analysis!A2&" "&Analysis!TEXT(B2,"hh:mm") &" minutes"``

But why not just Custom Format your cell(B2)?
"Median Time is "hh:mm" minutes"

lenze

Last edited:

#### Smokeyham

##### Board Regular
Looks like you're missing some apostrophes and have the wrong format:

=Analysis!A2&TEXT(Analysis!B2," hh:mm")

Hope that helps,

Just what I needed!! Thanks so much.

Gordon

Replies
3
Views
241
Replies
33
Views
307
Replies
5
Views
301
Replies
4
Views
123
Replies
0
Views
507

1,191,719
Messages
5,988,290
Members
440,148
Latest member
sandy123

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back