Formatting Concatenated results in 1 cell

**David_S_Walker** · 07-18-2011, 06:51 AM

Hello,

I have a concatenate formula which draws together a time and a statement from different spreadsheets.

The result should look like:

"08:45 - Name of Bill" where time is drawn from 1 cell and the actual name of the bill is drawn from another.

How do I format the result so that the time remains formatted as time and does not convert to a decimal? I guess it is the use of "Custom" formats but can't work out how to do it.

Any suggestions?

Thanks & best regards,

David

**Domski** · 07-18-2011, 07:10 AM

Something like this:

=TEXT(A1,"hh:mm")&" - "&B1

Dom

**David_S_Walker** · 07-18-2011, 07:26 AM

Thanks Dom - That works for me!

**David_S_Walker** · 07-18-2011, 08:02 AM

OK so whilst that is sorted, when I was only linking in the Time value I was able to conditionally format it so it would turn green if before a certain time etc. But now it is combining Time and a Text statement the conditional format seems to have stopped.

So can you apply a conditional format to an instance like this based on the first part of the resultant display eg 08:45?

Best regards,

David

**Domski** · 07-18-2011, 08:22 AM

If the cell containing just the time is in the same workbook it's probably just as easy to base your conditional formatting on that using a formula.

Dom

**David_S_Walker** · 07-18-2011, 08:30 AM

Unfortunately it is in different workbook so I might be out of luck.

Thanks for your repsonse.

**Domski** · 07-18-2011, 08:59 AM

If your concatenated formula is in C1 you can return the serial time value from the start of it using =LEFT(C1,5)*1

There's no reason why you couldn't build that into a conditional formatting formula.

Dom