How do I insert a symbol as part of a formula

[Rhonda]

Hey Guys,

Is it possible for me to insert a "smiley face" or "sad face" symbol in
blank cell next a cell that has a formula in it? For instance, if the time
in cell A1 is 2:00 (minutes) but the limit is 1:30, how can I put a "sad
face" symbol in the blank cell B1 automatically, see below for an example:

Cell A1 has a time of 2:20 PM (the maximum time should be 1:30 - this limit
is not in the spreadsheet). I want to have cell B1 automatically populate a
"sad face" because the maximum was over 50 minutes OR

Cell A1 has a time of 1:20 pm (the maximum time should be 1:30 - this limit
is not in the spreadsheet). I want to have cell B1 automatically populate a
"smiley face" because the limit was under the required 1:30.

Does this make sense? This is a spreadsheet that calculates the time
someone arrives to a clinic and time they leave, the maximum should be 1 hour
30 minutes for the person to go through the procedure but sometimes it does
not happen within that time, I want the spreadsheet to show when it does and
doesn't with the "smiley face" symbol.

I hope this was explained correctly, any help would be greatly appreciate!

Rhonda

 

[Barb Reinhardt]

Why not use conditional formatting. If all is good, format it to be GREEN
and if all is BAD, make it red. It's under FORMAT -> Conditional Format

 

[Dave F]

I don't really understand your example but why not just have "exceeded time
limit" or "didn't exceed time limit" appear depending upon the situation.

 

[PCLIVE]

Change the font for cell B1 to Wing Dings. Then use the letter "J" for a
happy face and the letter "L" for a sad face.

=IF(A1-0.0625>0,"L","J")

Hope this helps.
Paul

 

[Bernie Deitrick]

Rhonda,

Download a font that includes smiley faces as individual characters, then write the formula to
return the correct character number, and format the cell for that font. Use a formula like

=IF(A1>VALUE("1:30:00"),CHAR(65),CHAR(66))

HTH,
Bernie
MS Excel MVP

 

[romelsb]

excuse me guys....how to do it in the formula if A1 must be the current
ticking time....thanks

 

[Bernie Deitrick]

To have a current ticking time, you need to use macros. Post your email address or reply to me
privately, and I will send you an example workbook with a timer that you could use.

HTH,
Bernie
MS Excel MVP

 

[Rhonda]

Bernie,

That would be great! My email is (e-mail address removed). The formula you sent
did work but I would like to make the smiley or sad face colored, can I do
that? to emphasize (good versus bad)? I really appreciate your help on this.

 

[Bernie Deitrick]

Sent.....

HTH,
Bernie
MS Excel MVP

Bernie,

That would be great! My email is (e-mail address removed). The formula you sent
did work but I would like to make the smiley or sad face colored, can I do
that? to emphasize (good versus bad)? I really appreciate your help on this.

 

[Lori]

You can also insert a smiley face (☺☻), by typing ALT+1 or 2 on the
numeric key pad)