Fomatting part of a formula to be Bold font

[KimberlyC]

Hi,
Is there a way to add formatting to this formula to make the "Time Period: "
bold and leave the rest of it in regular text?

="Time Period: "& TEXT(From2,"mm/dd/yyyy")&" - "&TEXT(To2,"mm/dd/yyyy")

Thanks in advance for you help!!
Kimberly

 

[Harlan Grove]

KimberlyC wrote...

Is there a way to add formatting to this formula to make the "Time Period: "
bold and leave the rest of it in regular text?

="Time Period: "& TEXT(From2,"mm/dd/yyyy")&" -

"&TEXT(To2,"mm/dd/yyyy")
....

No. Excel doesn't provide such functionality.

 

[Max]

If you find it worth the trouble, think the result cell could be "dressed
up" to appear as desired, via a disguised textbox which sits
directly/exactly over the result cell.

Here's the play ..

Assume From2, To2 are named ranges referring to A1 & B1 respectively, and
that C1 contains just:
=TEXT(From2,"mm/dd/yyyy")&" - "&TEXT(To2,"mm/dd/yyyy")

Draw a textbox to fit exactly over C1
(hold down Alt key to resize/fit to grid)

Right-click on textbox > Format textbox > Colors and Lines tab
Set the Fill to: No Fill, Line color to: No Line

Enter the text: "Time Period:"
Set the font to bold (or whatever you want)
and Text alignment to "Horizontal: Left"

Stretch / resize C1 (i.e. col C) so that both the text in the textbox
and the result in C1 shows through
(Right-align the evaluated value in C1)

If A1 contains: 01-May-2005, B1 contains: 02-May-2005
then C1 will appear as:
Time Period: 05/01/2005 - 05/02/2005
(with "Time Period:" in bold/formatted as desired)

Note tha with the textbox fitted over C1,
you need to use the arrow keys to navigate to select cell C1

Sample file for the above is at:
http://flypicture.com/p.cfm?id=44172
(right-click on the link "Download File" at the top of the page)
File: KimberlyC_wksht.xls

 

[JE McGimpsey]

XL formulae can't change formatting, so you can't do this with a
worksheet function. However, you can, using an event macro.

Put this in your worksheet code module (right-click on the worksheet tab
and choose View Code):

Private Sub Worksheet_Calculate()
Const sPREFIX As String = "Time Period: "
With Range("A1")
.Font.Bold = False
Application.EnableEvents = False
.Value = sPREFIX & _
Format(Range("From2").Value, "mm/dd/yyyy - ") & _
Format(Range("To2").Value, "mm/dd/yyyy")
Application.EnableEvents = True
.Characters(1, Len(sPREFIX)).Font.Bold = True
End With
End Sub

 

[David McRitchie]

Very good John, I didn't pick up on the fact that the formula in
cell A1 (assigned in macro), that can't be partially formatted to
bold wasn't needed for calculations and could simply be eliminated
permanently as a formula and replaced with a text string that
can be partially formatted and whose value is calculated and
formatted in an event macro.

 

[KimberlyC]

Thank you all!!

 

[Max]

You're welcome!

 

[Hash]

Kimberly -

Is there a particular reason for using only one cell?

Use 2 if you can. "Time Period:" in one formatted bold.

Text() in the next. Align and size as desired. Much easier.

....best, Hash