I have a field where I want the display to be in text, but when I sort, I
want to sort by date. Here's my dilema, though. I sometimes need to report
specific dates (Jan 13), but other times, just the month (Jan). Then, on
the sort, I want it to return them grouped together.

Currently I have a custom date format of 'mmm dd' so my display is 'Jan 13'.
When I just type in 'Jan', it automatically changes it to 'Jan 01' to match
my format...don't want that.

So, how can I get a format that will display 'Jan 13' or 'Jan' if it's just
a month (but still let me sort on that column so all the Jan's are together,
all the Feb's are together, etc.)

Thanks in Advance,
Dave

You need to use a helper column that identifies the numeric value of the
month

=Month(B3) in C3 for example.

Then sort on the column with this formula.

I am not sure how a format would decide when it is appropriate to display
Jan 01 vice Jan. In any event, conditional formatting in a format string is
very limited - it is really restricted to magnitude as a decision criteria.

Conditional formatting under the format menu does not control the number
format.

--
Regards,
Tom Ogilvy


"Dave "IT"" wrote in message
.. .
I have a field where I want the display to be in text, but when I sort, I
want to sort by date. Here's my dilema, though. I sometimes need to
report
specific dates (Jan 13), but other times, just the month (Jan). Then, on
the sort, I want it to return them grouped together.

Currently I have a custom date format of 'mmm dd' so my display is 'Jan
13'.
When I just type in 'Jan', it automatically changes it to 'Jan 01' to
match
my format...don't want that.

So, how can I get a format that will display 'Jan 13' or 'Jan' if it's
just
a month (but still let me sort on that column so all the Jan's are
together,
all the Feb's are together, etc.)

Thanks in Advance,
Dave

As far as I'm aware when you format a date eg 13-jan-2003
to dd-mmm, excel forgets or loses the year and replaces it
with the current system year (though not sure why yours is
2001).

I would say you could have a variable which is the full
date and reference that when sorting.
and possibly use the format or datepart functions to
display the other.

HTH
-----Original Message-----
I have a field where I want the display to be in text,
but when I sort, I
want to sort by date. Here's my dilema, though. I
sometimes need to report
specific dates (Jan 13), but other times, just the month
(Jan). Then, on
the sort, I want it to return them grouped together.

Currently I have a custom date format of 'mmm dd' so my
display is 'Jan 13'.
When I just type in 'Jan', it automatically changes it
to 'Jan 01' to match
my format...don't want that.

So, how can I get a format that will display 'Jan 13'
or 'Jan' if it's just
a month (but still let me sort on that column so all the
Jan's are together,
all the Feb's are together, etc.)

Thanks in Advance,
Dave


.

Formatting after data is stored in a cell does not change the current value
stored in a cell.

--
Regards,
Tom Ogilvy



"FRED" wrote in message
...
As far as I'm aware when you format a date eg 13-jan-2003
to dd-mmm, excel forgets or loses the year and replaces it
with the current system year (though not sure why yours is
2001).

I would say you could have a variable which is the full
date and reference that when sorting.
and possibly use the format or datepart functions to
display the other.

HTH
-----Original Message-----
I have a field where I want the display to be in text,
but when I sort, I
want to sort by date. Here's my dilema, though. I
sometimes need to report
specific dates (Jan 13), but other times, just the month
(Jan). Then, on
the sort, I want it to return them grouped together.

Currently I have a custom date format of 'mmm dd' so my
display is 'Jan 13'.
When I just type in 'Jan', it automatically changes it
to 'Jan 01' to match
my format...don't want that.

So, how can I get a format that will display 'Jan 13'
or 'Jan' if it's just
a month (but still let me sort on that column so all the
Jan's are together,
all the Feb's are together, etc.)

Thanks in Advance,
Dave


.