Formatting cell

E

Excel Jedi

I'm trying to bullet proof a spreadsheet for other users. I would like to
format a cell ($E13) to show the month (Jan) when only entering a "1".
However, when I custom format that cell "mmm", it changes the cell value to
1/1/1900 which affects my conditional formatting (Condition #1:
=AND((MOD(ROW(),2)=0),$E13<($Q$1),$E13<>"") for bold red text and alternating
row colors; Condition #2: =MOD(ROW(),2)=0 for alternating row colors;
Condition #3: =AND($E13<($Q$1-1),$E13<>0)) for bold red text. I've been
working on this too long and may be over complicating things and staring
right at the solution. Many thanks.
 
G

Gary''s Student

Consider using two cells, say in A1 we enter 1 and in another cell enter:

=TEXT(DATE(2010,A1,1),"MMM")

Which will display Jan

You could also use a macro which would automatically convert the user input
from numeric to text-month in place.
 
E

Excel Jedi

Unfortunately, using another cell isn't the best option for the purpose of
this spreadsheet. Is it possible to incorporate some kind of format formula
like you suggested into my existing conditional format formulas? So far I
have not been successful.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top