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.

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.
--
Gary''s Student - gsnu201001


"Excel Jedi" wrote:

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.

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.

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu201001


"Excel Jedi" wrote:

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.